CODEWITHSUNDEEP
sqlgoogle-bigquery
Flitschi
Flitschi

Reputation: 93

Filter using WHERE but not GROUP BY

I am pretty new to SQL, so it might be obvious but I am stuck. I have a table where I am counting song plays based on a few conditions. My problem is that one of the columns (songlength) I want to filter on but I dont want to group by it in the output because it will give the wrong output.

Here is my pseudo code:

SELECT COUNT(songid) AS Plays, songlength, artist
FROM MyTable
WHERE artist = Justin AND songlength > 10000
GROUP BY artist
HAVING Plays > 0
ORDER BY Plays DESC

How would I achieve this best?

Upvotes: 2

Views: 1188

Answers (1)

Mohammad
Mohammad

Reputation: 678

Try writing this way. When you put some non aggregated field in select list then it is mandatory to put same columns in group by. The solution you are looking, below code can help. Try and let me know

SELECT COUNT(songid) AS Plays, artist
FROM MyTable
WHERE artist = 'Justin' AND songlength > 10000
GROUP BY artist
HAVING Plays > 0
ORDER BY Plays DESC

Upvotes: 2

Related Questions