October 31, 2007 at 9:07 am
Hello,
Im using this query to calculate the total number of marks in an exam per subject:
SELECT STU_ID, SUB_NAME AS 'SUBJECT', SUM(MARKS) AS [TOTAL MARKS]
FROM EXAM_RESULTS
WHERE STU_ID = '1856805'
GROUP BY STU_ID, SUB_ID
ORDER BY SUB_ID
In addition I need to to get the total number of marks as another column which will be the sum of all the marks in each subject. These results will then be displayed in a datagrid so I dont want to use COMPUTE.
any ideas?
many thanks
October 31, 2007 at 9:28 am
Something like this ought to work for you.
IF OBJECT_ID('TempDB..#Marks','u') IS NOT NULL
DROP TABLE #Marks
GO
CREATE TABLE #Marks
(
Stu_ID INT,
Sub_Name VARCHAR(30),
Marks INT
)
INSERT INTO #Marks
SELECT '1','MTH','90' UNION ALL
SELECT '1','ENG','92' UNION ALL
SELECT '1','CHM','85' UNION ALL
SELECT '1','BIO','79' UNION ALL
SELECT '2','MTH','99' UNION ALL
SELECT '2','ENG','91' UNION ALL
SELECT '2','CHM','89' UNION ALL
SELECT '2','BIO','100' UNION ALL
SELECT '3','MTH','85' UNION ALL
SELECT '3','ENG','83' UNION ALL
SELECT '3','CHM','97' UNION ALL
SELECT '3','BIO','96'
SELECT
STU_ID,
m.SUB_NAME AS 'SUBJECT',
SUM(m.MARKS) AS [TOTAL MARKS],
t1.Subject_marks
FROM #Marks m,
(--Derived table t1 sums the subject totals first
SELECT
SUB_Name,
Subject_Marks = SUM(Marks)
FROM #Marks
GROUP BY Sub_name
) t1
WHERE m.sub_name = t1.sub_name
AND m.stu_id = '1'
GROUP BY STU_ID, m.SUB_Name,t1.subject_marks
ORDER BY m.SUB_Name
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 31, 2007 at 9:35 am
Ok, I just realized you wanted a column to show the sum of the students marks in all subjects, not the sum of all marks of all students in each subject. If that is the case this one will work.
SELECT
m.STU_ID,
m.SUB_NAME AS 'SUBJECT',
SUM(m.MARKS) AS [SUBJECT MARKS],
t1.TOTAL_MARKS
FROM #Marks m,
(--Derived table t1 sums the subject totals first
SELECT
stu_id,
TOTAL_Marks = SUM(Marks)
FROM #Marks
GROUP BY stu_id
) t1
WHERE m.stu_id = t1.stu_id
AND m.stu_id = '1'
GROUP BY m.STU_ID, m.SUB_Name,t1.total_marks
ORDER BY m.SUB_Name
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 31, 2007 at 9:53 am
Greg, what can I say but THANKS ALOT, it worked perfectly.
Thanks again 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply