sql - 如何使用 GROUP BY 为不同列获取多个 SUM()

Question

这就是我所拥有的:

SELECT     
    SUBJECT_ID, SUM(SOMETABLE.COLUMN) AS HOURS, POINTS, SEMESTER_ID
FROM       
    SOME_TABLES
WHERE     
    (GROUP = (SELECT TOP (1) GROUP
              FROM SOMETABLE2
              WHERE (STUDENT_ID = 123)))
GROUP BY 
    SUBJECT_ID, POINTS, SEMESTER_ID
HAVING      
    (SUBJECT_ID = 782)

此查询返回:

我需要得到这个结果:

为了获得结果，我使用以下查询:

SELECT     
    SUBJECT_ID, SUM(SOMETABLE.COLUMN) AS HOURS,  
    SUM(SOMETABLE3.COLUMN) AS POINTS, SEMESTER_ID
FROM
    SOME_TABLES
WHERE     
    (GROUP = (SELECT TOP (1) GROUP
              FROM SOMETABLE2
              WHERE (STUDENT_ID = 123)))
GROUP BY 
    SUBJECT_ID, SEMESTER_ID
HAVING      
    (SUBJECT_ID = 12)

但它返回 SUM 而不包含 GROUP BY 语句 - 就像第二个屏幕截图一样，但有两次 16 分，而每学期应该有两行 8 分.

如何获得 SEMESTER_ID 的正确积分？有带有示例数据的脚本 在这篇文章下的评论中。

Answer 1

试试这个:

SELECT
     SUBJECT_ID,SEMESTER)ID
    ,SUM(HOURS) as HOURS
    ,SUM(POINTS) as POINTS
FROM SOME_TABLES
WHERE SUBJECT_ID = 12
GROUP BY
     SUBJECT_ID,SEMESTER)ID