How to get multiple SUM () for different columns using GROUP BY

Question

This is what I have:

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 these results, I use 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 the GROUP BY operator - as in the second screenshot, but twice in 16 points, while in the semester there should be two rows with 8 points.

How to set POINTS to SEMESTER_ID correctly? There is a script with sample data in a comment on this post.

Answer 1

You can use OVER , something like:

QUERY

 select distinct Id, sum(Hours) over (partition by SimId) Hours, sum(Points) over (partition by SimId) Points, SimId from #t 

SAMPLE DATA

 create table #t ( Id INT, Hours INT, Points INT, SimId INT ) insert into #t values (787,100,0,214858), (787,0,8,214858), (787,100,8,233562), (787,0,0,233562) 

OUTPUT

 Id Hours Points SimId 787 100 8 214858 787 100 8 233562 

UPDATE

This is because you did not provide sample data that you can do with CTE in the following:

 WITH cte AS ( 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) ) SELECT DISTINCT SUBJECT_ID, sum(Hours) over (partition by SEMESTER_ID) Hours, sum(Points) over (partition by SEMESTER_ID) Points, SEMESTER_ID FROM cte 

UPDATE 2

You can create #temp_table and insert data from your selection request, after using CTE in the following:

 ;WITH cte AS ( SELECT * FROM #temp_table ) SELECT DISTINCT SUBJECT_ID, sum(Hours) over (partition by SEMESTER_ID) Hours, sum(Points) over (partition by SEMESTER_ID) Points, SEMESTER_ID FROM cte 

Answer 2

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 

Answer 3

Consider using CROSS APPLY to get the sum for each column.

 SELECT SUBJECT_ID, studentHours.hoursWorked AS HOURS, studentPoints.pointsEarned AS POINTS, SEMESTER_ID FROM SOME_TABLES CROSS APPLY ( SELECT SUM(someColumn) as hoursWorked FROM SOMETABLE2 WHERE STUDENT_ID = 123 ) studentHours CROSS APPLY ( SELECT SUM(someColumn) as pointsEarned FROM SOME_POINTS_TABLE WHERE STUDENT_ID = 123 ) studentPoints WHERE SUBJECT_ID = 12 GROUP BY SUBJECT_ID, SEMESTER_ID 

Answer 4

With a sample table, the query should look like this:

WITH cte AS (SELECT SUBJECT_ID, HOURS, POINTS, SEMESTER_ID FROM SAMPLE_TABLE2 GROUP BY HOURS, POINTS, SUBJECT_ID, SEMESTER_ID ) SELECT DISTINCT SUBJECT_ID, sum(Hours) over (partition by SEMESTER_ID, SUBJECT_ID) Hours, sum(Points) over (partition by SEMESTER_ID, SUBJECT_ID) Points, SEMESTER_ID FROM cte

The key was:
sum(Hours) over (partition by SEMESTER_ID, SUBJECT_ID ) hours
and:
GROUP BY HOURS, POINTS, SUBJECT_ID, SEMESTER_ID inside INNER QUERY .

Thanks @Stanislovas Kalašnikovas!

Answer 5

Not sure if this will help, but the TOP clause usually does not require parentheses. Change And for best practice, the ORDER BY clause is recommended when using the TOP clause. You can also try ORDER BY along with LIMIT. This can help.