对多个表和联接使用groupby的sql计数

7tofc5zh 于 2021-08-09 发布在 Java

关注(0)|答案(3)|浏览(277)

我有3张table（学校、学生、活动），就像这样：
学校

学生

活动

我想按性别列出2020年每所学校的所有活动和参与者人数，如下表所示：

我做了一个这样的查询，但它没有按我想要的那样工作：

SELECT Sc.SchoolName, A.ActivityName, Sc.Year COUNT(Gender)
FROM School Sc
JOIN Student S ON Sc.SchoolID=S.SchoolID
JOIN Activity A ON S.ActivityID=A.ActivityID
GROUP BY Gender

我怎样才能解决这个问题？你能给我一个解决办法吗？

sql Join group-by count pivot

来源：https://stackoverflow.com/questions/62349415/sql-counting-using-group-by-with-multiple-tables-and-joins

3条答案

按热度按时间

ig9co6j11#

您需要条件聚合：

SELECT 
    Sc.SchoolName, 
    A.ActivityName, 
    Sc.Year, 
    SUM(CASE WHEN S.Gender = 'F' THEN 1 ELSE 0 ENd) F,
    SUM(CASE WHEN S.Gender = 'M' THEN 1 ELSE 0 ENd) M
FROM School Sc
JOIN Student S  ON Sc.SchoolID  = S.SchoolID
JOIN Activity A ON S.ActivityID = A.ActivityID
GROUP BY 
    Sc.SchoolName, 
    A.ActivityName, 
    Sc.Year

赞(0）回复(0）举报 2021-08-09

btqmn9zl2#

SELECT 
    Sc.SchoolName, 
    A.ActivityName, 
    Sc.Year, 
    COUNT(CASE WHEN S.Gender = 'F' THEN StudentID END) as F,
    COUNT(CASE WHEN S.Gender = 'M' THEN StudentID END) as M
FROM School Sc
INNER JOIN Student S  ON Sc.SchoolID  = S.SchoolID
INNER JOIN Activity A ON S.ActivityID = A.ActivityID
WHERE YEAR LIKE '2020-%'
GROUP BY 
    Sc.SchoolName, 
    A.ActivityName, 
    Sc.Year

赞(0）回复(0）举报 2021-08-09

jrcvhitl3#

请使用下面的查询，

SELECT Sc.SchoolName, Sc.Year, A.ActivityName,
      case when Sc.Gender = 'M' then count(1) end as M,
      case when Sc.Gender = 'F' then count(1) end as F
FROM School Sc
JOIN Student S ON Sc.SchoolID=S.SchoolID
JOIN Activity A ON S.ActivityID=A.ActivityID
GROUP BY Sc.SchoolName, Sc.Year, A.ActivityName;

赞(0）回复(0）举报 2021-08-09

我来回答

对多个表和联接使用groupby的sql计数

3条答案

相关问题

热门标签

最新问答