Get the max count after joining two table

SQL> select * from produced;

  P_NO       M_NO

   121          1
   122          2
   123          3
   124          4
   125          5
   125          6

6 rows selected.

SQL> select * from production;

  P_NO NAME                 ADDRESS

   121 Vajreshwari          Bangalore
   122 Old_films            Chennai
   123 Raj_movies           Chennai
   124 Sri_movies           Bangalore
   125 Golden_movies        Bangalore

SQL>

SQL> select max(mcnt) from
2 (select p.p_no,p.name,count(d.m_no) as mcnt
3 from production p, produced d
4 where p.p_no=d.p_no
5 group by p.p_no,p.name);

MAX(MCNT)

     2

SQL> select p_no, name,max(mcnt) from
2 (select p.p_no,p.name,count(d.m_no) as mcnt
3 from production p, produced d
4 where p.p_no=d.p_no
5 group by p.p_no,p.name);
select p_no, name,max(mcnt) from
*
ERROR at line 1:
ORA-00937: not a single-group group function

Help me to solve the following query

List the details of production companies producing maximum movies.

  1. This is homework question. Pelase show us what you have tried. Sorry we are not doing your homework for you.

  2. You are using Oracle.SQLTeam.com is for Microsoft SQL Server

Okay. I solved it