getting a count of movies - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

getting a count of movies

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-14 : 09:12:18

I need to have a count of the movies in each genre
I have 2 tables movies and genre

I tried this, but it did not work
SELECT COUNT(*) AS ItemCount, genre.genreID, genre.genreDesc, movies.ID
FROM genre INNER JOIN
movies ON genre.genreID = movies.genreID
GROUP BY genre.genreID, genre.genreDesc, movies.ID

Dave
Helixpoint Web Development
http://www.helixpoint.com

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-14 : 09:21:37

not sure about your table structure but if movies.id is unique to each movie then your count is only going to return 1 because you're grouping by a field that is distinct. If this is the case then to solve your problem, remove the movies.id from your select and your group by statement and you should end up with a count by genreID

Mike
"oh, that monkey is going to pay"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-14 : 14:52:45

[code]
SELECT
COUNT(*) AS ItemCount,
genre.genreID,
genre.genreDesc
FROM
genre
INNER JOIN
movies
ON genre.genreID = movies.genreID
GROUP BY
genre.genreID,
genre.genreDesc

[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources