CODEWITHSUNDEEP
mysqlsqlgroup-by
Deses
Deses

Reputation: 1080

Group by and counting not returning what I expect

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! :)

Upvotes: 0

Views: 66

Answers (2)

arshovon
arshovon

Reputation: 13661

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;

Upvotes: 2

FuzzyTree
FuzzyTree

Reputation: 32402

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

Upvotes: 1

Related Questions