Davey D Davey D - 6 months ago 16
MySQL Question

SQL: Count the values of a column per record

I am stuck on creating a query.

I got a table "videos" with this structure:

id | artist | song | hits


records are like:

1 | Rihanna | Song1 | 400
2 | Rihanna | Song2 | 100
3 | Prince | Song45 | 300
4 | The Police | Song456 | 1000


This is my non-working query:

SELECT DISTINCT artist, SUM(hits)
FROM videos
ORDER BY hits DESC


The result is:

Rihanna | 1800


But I'd like a result like this:

Rihanna | 500
Prince | 300
The Police | 1000


What am I doing wrong here?

Answer
SELECT artist, SUM(hits) as hitscount
FROM videos
GROUP BY artist
ORDER BY hitscount DESC

added suggestions from the comments. thanks!

Comments