Group by and counting not returning what I expect

Question

I Have this table where I need to know how many albums an artist has churned during his/her career, I'll post only one artist for brevity's sake.

"artist_ID" "song_ID" "album_ID" "touring"
"57"      "98"       "48"      "No"        
"57"      "99"       "48"      "No"        
"57"      "100"      "48"      "Sí"        
"57"      "101"      "48"      "No"        
"57"      "102"      "48"      "No"        
"57"      "103"      "48"      "No"        
"57"      "104"      "48"      "No"        
"57"      "105"      "48"      "No"        
"57"      "106"      "48"      "No"        
"57"      "279"      "163"     "No"        
"57"      "280"      "163"     "No"        
"57"      "281"      "163"     "No"        
"57"      "380"      "241"     "No"        
"57"      "381"      "241"     "No"

Then to get the data I go by this:

SELECT artist_ID,
    count(*) AS churned_albums
FROM relation
GROUP BY group_ID,
    album_ID;

And I expect the following data:

"artist_ID" "churned_albums"           
"57"      "3"

But alas, I get this:

"artist_ID" "churned_albums"
"57"      "9"             
"57"      "3"             
"57"      "2"

It's counting every song for every album and I don't know how to tell MySQL to ignore the darn songs and just count albums. I also tried to count(artist_ID) instead of count(*) and several other permutations, but nothing seems to work.

This question is closely related to How to use count and group by at the same select statement and Using group by on multiple columns but they did not help. :(

Thanks for the help! :)

@Tibrogargan woops! Forgot to edit those. The table is in another language and I forgot to translate those strings. :P — Deses, Apr 07 '16 at 23:19

score 2 · Accepted Answer · answered Apr 07 '16 at 23:03

2

I have created a sqlfiddle for visualization of your desired query. Have a look here: http://sqlfiddle.com/#!9/d1372/2

The query I have used:

select group_id, count(distinct album_id) AS churned_albums
from relations
group by group_id;

answered Apr 07 '16 at 23:03

arshovon

13,270
9
51
69

I did the same query on sqlfiddle but you were faster :P – Gustavo Rubio Apr 07 '16 at 23:12
Thank you Fuzzy and arsho! Also thanks for letting me know about SQL Fiddle, it'll come in handy for future questions. :) – Deses Apr 07 '16 at 23:20

score 1 · Answer 2 · answered Apr 07 '16 at 22:55

1

You can use count(distinct album_id) to count the # of unique albums per group_id

select group_id,
    count(distinct album_id) as churned_albums
from relation
group by group_id

answered Apr 07 '16 at 22:55

FuzzyTree

32,014
3
54
85

Group by and counting not returning what I expect

2 Answers2