Please help with this query problem

[Kordon]

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.

[SDas]

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:

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:

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):

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.

[Kordon]

Thank you so much, this worked.

[Kordon]

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

[SDas]

Using the first query, ADD THE SECOND LINE BELOW:

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?

[Kordon]

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