CODEWITHSUNDEEP
sqlsubquery
John
John

Reputation: 4543

SQL Sub query doubt

i'm a bit stuck and would appreciate the help..... i'm sure i need to use a sub query but when i try i m getting errors saying returning more than one row....

So this is the code

SELECT to_char(p.releasedate, 'YYYY') AS releaseyear , t.genre
, COUNT(*) AS "Number of awards"
FROM dbf11.film p
JOIN dbf11.genre t
ON p.filmID = t.filmID
GROUP BY p.releasedate, t.genre
ORDER BY p.releasedate, t.genre ;

What i'm trying to do is.... for each year display the no. of films for each genre

What i'm currently getting is

2010 Action 1
2010 Comedy 1
2010 Comedy 1
2011 Action 1
2011 Action 1
2011 Action 1

What i want it to display is 

2010 Action 1
2010 Comedy 2
2011 Action 3

Upvotes: 1

Views: 243

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171589

SELECT to_char(p.releasedate, 'YYYY') AS releaseyear, 
    t.genre.
    COUNT(*) AS "Number of awards"
FROM dbf11.film p
INNER JOIN dbf11.genre t ON p.filmID = t.filmID
GROUP BY to_char(p.releasedate, 'YYYY'), t.genre
HAVING COUNT(*) > 1
ORDER BY to_char(p.releasedate, 'YYYY'), t.genre;

Upvotes: 7

cordsen
cordsen

Reputation: 1691

Try this

SELECT to_char(p.releasedate, 'YYYY') AS releaseyear , t.genre
, COUNT(*) AS "Number of awards"
FROM dbf11.film p
JOIN dbf11.genre t
ON p.filmID = t.filmID
GROUP BY to_char(p.releasedate, 'YYYY'), t.genre
ORDER BY releaseyear, t.genre

Upvotes: 1

Related Questions