mysql - 分组并计数不返回我期望的

Question

我有这张表，我需要知道一位艺术家在他/她的职业生涯中发行了多少张专辑，为了简洁起见，我将只发布一位艺术家。

"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"        

然后通过以下方式获取数据:

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

我希望得到以下数据:

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

但是，唉，我明白了:

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

它计算每张专辑的每首歌，我不知道如何告诉 MySQL 忽略该死的歌曲而只计算专辑。我还尝试了 count(artist_ID) 而不是 count(*) 和其他几个排列，但似乎没有任何效果。

这个问题与How to use count and group by at the same select statement密切相关和 Using group by on multiple columns但他们没有帮助。 :(

感谢您的帮助! :)

Answer 1

我已经创建了一个 sqlfiddle 用于可视化您所需的查询。在这里看看:http://sqlfiddle.com/#!9/d1372/2

我用过的查询:

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