-
Please help with this query problem
Hello
I've found this forum while trying to find a resolution to a problem that has been haunting me the entire day today. I cannot get the SUM to properly calculate values when joining two tables together.
I have two tables:
Table A (Math)
----------
Student ID, Marks (out of 100 each)
-----------------------------------------
20001, 75
20001, 85
------------------------------------------
Table B (Science)
----------
Student ID, Marks
------------------------------------------
20001, 60
20001, 40
------------------------------------------
What I am trying to do is get a query that will SUM the marks for math and science for this student and display them as follows
Math, Science
----------------
160, 100
My query calculates the math fine, but science doesn't add up.
Select SUM(a.Marks) as "Math", SUM(b.Marks)as "Science"
from Table A as a
JOIN Table B as b on a.StudentId = b.StudentId
Where a.StudentId = 20001
I've tried different variations of the query, and if I take it apart it sums both properly, but when joined the Science is wrong.
-
Kordon, the problem is that SQL Server is doing the Join first (which is generating duplicate rows) and then doing the summarization (of those duplicate rows). So what you have to do is make it do the summarization first and then the join:
Code:
SELECT MATH_TOTAL, SCIENCE_TOTAL
FROM (SELECT Student_ID, SUM(MARKS) AS MATH_TOTAL
FROM math_table
WHERE STUDENT_ID = 20001
GROUP BY STUDENT_ID
) AS M
, (SELECT Student_ID, SUM(MARKS) AS SCIENCE_TOTAL
FROM science_table
WHERE STUDENT_ID = 20001
GROUP BY STUDENT_ID
) AS S
WHERE M.STUDENT_ID = S.STUDENT_ID
PS The above SQL can handle multiple STUDENT_ID values (if you change the WHERE clauses to IN(val1, val2, val3, etc.)). If you will always only have one STUDENT_ID, the SQL can be simplified a little bit like this:
Code:
SELECT MATH_TOTAL, SCIENCE_TOTAL
FROM (SELECT SUM(MARKS) AS MATH_TOTAL
FROM math_table
WHERE STUDENT_ID = 20001
) AS M
, (SELECT SUM(MARKS) AS SCIENCE_TOTAL
FROM science_table
WHERE STUDENT_ID = 20001
) AS S
Here is another approach (although I think the first one is a little better):
Code:
SELECT SUM(CASE CLASS_TYPE WHEN 'M' THEN MARKS
ELSE 0
END) AS MATH_TOTAL
SUM(CASE CLASS_TYPE WHEN 'S' THEN MARKS
ELSE 0
END) AS SCIENCE_TOTAL
FROM (SELECT 'M' AS CLASS_TYPE, MARKS
FROM math_table
WHERE STUDENT_ID = 20001
UNION ALL
SELECT 'S' AS CLASS_TYPE, MARKS
FROM science_table
WHERE STUDENT_ID = 20001
)
This will only work for 1 STUDENT_ID. To allow multiple values, add STUDENT_ID to all the SELECT clauses and a GROUP BY STUDENT_ID as the last line.
-
Thank you so much, this worked.
-
I guess I also have a followup question to this. Is it possible to SUM the two values together to get the total of Math and Science?
SUM() only accepts one parameter
-
Using the first query, ADD THE SECOND LINE BELOW:
Code:
SELECT MATH_TOTAL, SCIENCE_TOTAL
, MATH_TOTAL + SCIENCE_TOTAL AS TOTAL_TOTAL
FROM (SELECT Student_ID, SUM(MARKS) AS MATH_TOTAL
FROM math_table
WHERE STUDENT_ID = 20001
GROUP BY STUDENT_ID
) AS M
, (SELECT Student_ID, SUM(MARKS) AS SCIENCE_TOTAL
FROM science_table
WHERE STUDENT_ID = 20001
GROUP BY STUDENT_ID
) AS S
WHERE M.STUDENT_ID = S.STUDENT_ID
Is this what you wanted?
-
That's exactly it. In the meantime I had found a way to do this using a temp table but this seems a much better solution.
thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|