How get multiple SUM() for different columns with GROUP BY

Question

This is what I've got:

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)

This query returns:

I need to get this result:

To get that results I'm using this query:

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)

But it returns SUM without including GROUP BY statement - like on second screenshot but there is 16 points two times, while there should be two rows with 8 points per semester.

How to get correct POINTS to SEMESTER_ID? There is script with sample data in comment under this post.

Answer 1

Try this instead:

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