CODEWITHSUNDEEP
sql
Avondzon
Avondzon

Reputation: 1

SQL: Trying to SELECT COUNT one column and at the same time show another column

For homework I need to search the IMDb database and find in what year the most documentaries came out. So I got this query:

SELECT  START_YEAR, COUNT(*)
FROM MOVIE
WHERE GENRE = 'Documentary'
GROUP BY START_YEAR
ORDER BY COUNT(*) DESC

This is the result from the query:

START_YEAR  | COUNT
____________|_________
2017        | 52
2018        | 43
2015        | 39
...         | ...

But I need the table to show this:

GENRE      |  START_YEAR  |  COUNT
___________|______________|_________
Documentary| 2017         | 52
Documentary| 2018         | 43
Documentary| 2015         | 39
...        | ...          | ...

I keep getting an error when I add something to the SELECT-clause. I don't know what to do, I'm a beginner in SQL and everything in the query above is all I know about SQL lol Thanks for helping me!!

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Also add it to the GROUP BY:

SELECT GENRE, START_YEAR, COUNT(*)
FROM MOVIE
WHERE GENRE = 'Documentary'
GROUP BY GENRE, START_YEAR
ORDER BY COUNT(*) DESC;

Or use a constant:

SELECT 'Documentary' as GENRE, START_YEAR, COUNT(*)
FROM MOVIE
WHERE GENRE = 'Documentary'
GROUP BY START_YEAR
ORDER BY COUNT(*) DESC;

Or use an aggregation function:

SELECT MAX(GENRE) as GENRE, START_YEAR, COUNT(*)
FROM MOVIE
WHERE GENRE = 'Documentary'
GROUP BY START_YEAR
ORDER BY COUNT(*) DESC;

Upvotes: 1

Related Questions