Need help using GROUP BY clause
I have two tables
A. TEST_SUBJECTS_TBL with the following columns . This table contains the subjects in a test
1. TEST_SUBJECT_ID PK
2. SUBJECT_ID FK
3. TEST_ID FK
4. PM // This is the passing marks for the subject
B. TEST_MARKS_TBL with the following columns This table stores the marks scored by students for each subject
1. TEST_SUBJECT_ID FK
2. STUDENT_ID FK
3. MARKS_OBTAINED
I need a query which gives me the max, min,avg marks obtained in each subject for a test and the total number of students who have passed in the subject
The query output should be something like this
_
SubjectID MaxMarks MinMarks AvgMarks TotalStudentsPassed
_
1 90 30 44 11 6
2 80 24 22 33 8
......
......
I can use a groupby to find max,min and avg marks but finding total students passed is posing a problem.
[883 byte] By [
Vihutuo] at [2007-12-24]
Use a SUM and a Case statement to find the Total Passing students. Something like:
Sum( Case when marks_obtained >= pm then 1 end )
or some other expression that evaluates to true when you want to count the student.
TRY THIS
CREATETABLE TEST_SUBJECTS_TBL (
TEST_SUBJECT_ID
INTIDENTITY
(1,1)PRIMARYKEY
, SUBJECT_ID
INT
, TEST_ID
INT
, PM
INT
)
CREATETABLE TEST_MARKS_TBL (
TEST_SUBJECT_ID
INT
, STUDENT_ID
INT
, MARKS_OBTAINED
INT
)
INSERTINTO TEST_SUBJECTS_TBL
SELECT 1,1,35 UNIONALL
SELECT 2,1,35 UNIONALL
SELECT 1,2,35 UNIONALL
SELECT 2,2,35 UNIONALL
SELECT 3,2,35
INSERTINTO TEST_MARKS_TBL
SELECT 1,1,40 UNIONALL
SELECT 1,2,35 UNIONALL
SELECT 1,3,80 UNIONALL
SELECT 1,4,30 UNIONALL
SELECT 1,5,25 UNIONALL
SELECT 2,1,80 UNIONALL
SELECT 2,2,15 UNIONALL
SELECT 2,3,10 UNIONALL
SELECT 2,4,40 UNIONALL
SELECT 2,5,45
SELECT SUBJECT_ID,MAX(MARKS_OBTAINED)AS MAX_MARKS,MIN(MARKS_OBTAINED)AS MIN_MARKS,AVG(MARKS_OBTAINED)AS AVG_MARKS,MAX(A.TOTSTUPASSED)AS TOTAL_PASSED
FROM TEST_MARKS_TBL M INNERJOIN TEST_SUBJECTS_TBL S ON
M.TEST_SUBJECT_ID=S.TEST_SUBJECT_ID AND S.TEST_ID=1
INNER
JOIN(SELECT
M.TEST_SUBJECT_ID,COUNT(STUDENT_ID)AS
TOTSTUPASSED FROM
TEST_MARKS_TBL M INNERJOIN TEST_SUBJECTS_TBL SON
M.TEST_SUBJECT_ID=S.TEST_SUBJECT_ID AND M.MARKS_OBTAINED>S.PM AND S.TEST_ID=1GROUPBY
M.TEST_SUBJECT_ID) A ON M.TEST_SUBJECT_ID=A.TEST_SUBJECT_ID
GROUPBY SUBJECT_ID
Yes, you should use SUM with CASE. Here is the complete query:
select
TM.SUBJECT_ID,
MaxMarks=max(TM.MARKS_OBTAINED),
MinMarks=min(TM.MARKS_OBTAINED),
AvgMarks=avg(TM.MARKS_OBTAINED),
TotalStudentPassed=(casewhen TM.MARKS_OBTAINED> TS.PMthen 1else 0end)
from
TEST_SUBJECTS_TBLTS(nolock)
innerjoin TEST_MARKS_TBL TM(nolock)
on TM.TEST_SUBJECT_ID= TS.TEST_SUBJECT_ID
groupby
TM.SUBJECT_ID
Ops! I have missed "SUM" in this statement:
select
TM.SUBJECT_ID,
MaxMarks=max(TM.MARKS_OBTAINED),
MinMarks=min(TM.MARKS_OBTAINED),
AvgMarks=avg(TM.MARKS_OBTAINED),
TotalStudentPassed=sum(casewhen TM.MARKS_OBTAINED> TS.PMthen 1else 0end)
from
TEST_SUBJECTS_TBL TS(nolock)
innerjoin TEST_MARKS_TBL TM(nolock)
on TM.TEST_SUBJECT_ID= TS.TEST_SUBJECT_ID
groupby
TM.SUBJECT_ID
