SQLTeam.com | Weblogs | Forums

Get the max count after joining two table

oracle

#1

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.


#2
  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


#3

Okay. I solved it