SQL COUNT to count all the numbers from another table

Question

I am writing a query to select the greatest number of total nominations among the movie of genre Comedy. I have the following query so far

SELECT m.movie_title, m.release_year, COUNT(*) 
FROM MOVIE m JOIN NOMINATION n 
ON (n.movie_title=m.movie_title AND n.release_year=m.release_year) 
WHERE m.genre='Comedy' GROUP BY m.movie_title, m.release_year;

If I execute the following query

SELECT m.movie_title, m.release_year 
FROM MOVIE m 
WHERE (m.genre='Comedy') GROUP BY m.movie_title,m.release_year);

It returns me with 3 results. My motive is to get one result with the highest count of nominations for movies with genre comedy.

Is my logic incorrect? I am still a beginner to sql (oracle11g) and teaching myself.

I have looked at multiple tutorials online but nothing has been able to help so far.

Thank you for all the help.

Your question is a bit vague. You are saying that your "motive is to get one result with the highest count of nominations for movies with genre comedy", however, in your query you are grouping by Movie_Title as well as Release_Year. This group by will not fetch you with just one row, it will provide you with movies with highest number of nominations across years. — Vaibhav, Oct 28 '14 at 06:37
The answers below are correct, however, if you wish to get only that one row (movie with highest nominations) you can remove "release_year" from GROUP BY and SELECT. — Vaibhav, Oct 28 '14 at 06:42

Lalit Kumar B · Accepted Answer · 2014-10-28T06:29:26.750

Lets understand it with the employee table example.

I have three rows which shows the count(*) of each department.

SQL> WITH data AS
  2    ( SELECT deptno, COUNT(*) rn FROM emp GROUP BY deptno
  3    )
  4  SELECT * FROM DATA
  5  /

    DEPTNO         RN
---------- ----------
        30          6
        20          5
        10          3

Now, I want one row which has the maximum count of departments.

SQL> WITH data AS
  2    ( SELECT deptno, COUNT(*) rn FROM emp GROUP BY deptno
  3    )
  4  SELECT * FROM DATA WHERE rn =
  5    (SELECT MAX(rn) FROM data
  6    )
  7  /

    DEPTNO         RN
---------- ----------
        30          6

SQL>

Or, also could be written as :

SQL> WITH data1 AS
  2    ( SELECT deptno, COUNT(*) rn FROM emp GROUP BY deptno
  3    ),
  4    data2 AS
  5    ( SELECT MAX(rn) max_rn FROM DATA1
  6    )
  7  SELECT d1.* FROM data1 d1, data2 d2 WHERE d1.rn = d2.max_rn
  8  /

    DEPTNO         RN
---------- ----------
        30          6

SQL>

So, in your case, use MAX function.

Other approach would be to use ANALYTIC function, ROW_NUMBER to assign RANK to each count(*) and then select the highest rank.

For example,

SQL> WITH data AS
  2    (SELECT deptno,
  3      row_number() OVER( ORDER BY COUNT(*) DESC) rn
  4    FROM emp
  5    GROUP BY deptno
  6    )
  7  SELECT deptno FROM DATA WHERE rn = 1
  8  /

    DEPTNO
----------
        30

SQL>

score 1 · Answer 2 · edited May 23 '17 at 10:26

Try something like this:

SELECT n.movie_title, n.release_year, COUNT(*) nominations
FROM NOMINATION n
LEFT JOIN MOVIE m ON n.movie_title=m.movie_title AND n.release_year=m.release_year
WHERE m.genre='Comedy'
GROUP BY n.movie_title,n.release_year
ORDER BY nominations DESC

Your approach would be:

Start with the table NOMINATION, because you want to count those.
Left Join the movie genre to every row in nomination and then filter by genre
Group your results to be able to count nominations for the same movie
Order the result set in descending order

To better understand the process please try the above query line for line and take a look at the results in between. Start by looking at the result, when just executing the first two lines. Then add the third line and execute again. And so on.

Update (after removing LIMIT):
Wrap the above SELECT Statement in

SELECT * FROM (
... above statement here ...
) WHERE ROWNUM=1;

See How do I limit the number of rows returned by an Oracle query after ordering? for further details.

score 1 · Answer 3 · answered Oct 28 '14 at 06:27

1

select * from (
  select
    movie_title,
    release_year,
    row_number() over (order by count(*) desc) rnk
  from
    movie m join
    nomination n using(movie_title, release_year)
  where
    m.genre = 'Comedy'
  group by
    movie_title,
    release_year
)
where
  rnk = 1

answered Oct 28 '14 at 06:27

René Nyffenegger

35,550
26
140
232

But as per question he just wants to get one result - Movie with highest number of nominations. You are grouping by Movie title as well as year. – Vaibhav Oct 28 '14 at 06:31
Without ERD and sample data, it's between hard and impossible to figure out the semantics of the tables. I took it that `movie_title` and `release_year` are the primary key of `movie` and that there is a 1:n relationship to `nomination`. – René Nyffenegger Oct 28 '14 at 06:35
Point! I have mentioned that as a comment to the question. Hope it helps OP. – Vaibhav Oct 28 '14 at 06:46

score 1 · Answer 4 · answered Oct 28 '14 at 06:33

If you order you query according to number of nominations and get the "first" one:

select movie_title, release_year, cnt
from (
    SELECT m.movie_title, m.release_year, count(n.movie_title) as cnt 
    FROM MOVIE m
    LEFT JOIN NOMINATIONS n
        ON n.movie_title=m.movie_title 
       AND n.release_year=m.release_year
    WHERE m.genre='Comedy' 
    GROUP BY m.movie_title,m.release_year
    ORDER BY COUNT(n.movie_title) desc
) 
where rownum = 1;

Another option is to use window functions and count(...) over ( ... )

score 0 · Answer 5 · answered Oct 28 '14 at 07:10

It is better to use a join

SELECT (select COUNT(*) from NOMINATION As COUNT), m.movie_title, m.release_year,n.movie_title,n.release_year FROM MOVIE m Join NOMINATION On n.movie_title=m.movie_title AND n.release_year=m.release_year WHERE m.genre='Comedy' GROUP BY m.movie_title,m.release_year

SQL COUNT to count all the numbers from another table

5 Answers5