Hi
Check the following Query... try like this.
SELECT CASE WHEN M.DispOrd=1 THEN 'CLASS '+ M.Class
WHEN M.DispOrd=2 THEN 'TOTAL NO OF STUDENTS '+ M.Class + ' ' + CAST(M.Count AS VARCHAR(10))
WHEN M.DispOrd=3 THEN 'STUDENTS PURCHASED '+ M.Class + ' ' + CAST(M.Count AS VARCHAR(10))
ELSE 'REMAINING STUDENTS '+ M.Class + ' ' + CAST(M.Count AS VARCHAR(10))
END 'Result'
FROM (SELECT DISTINCT Class,NULL 'Count, 1 'DispOrd'
FROM Student_Table
UNION ALL
SELECT Class ,COUNT(StudentID), 2
FROM Student_Table
GROUP BY Class
UNION ALL
SELECT Class, COUNT(StudentID), 3
FROM Purchase_Table
GROUP BY Class
UNION ALL
SELECT T.Class,T.Count, 4
FROM (SELECT S.Class, COUNT(S.StudentID)-COUNT(P.StudentID)
FROM Student_Table S
LEFT OUTER JOIN Purchase_Table P On P.studentID=S.StudentID
GROUP BY S.Class) T
) M
ORDER BY M.Class, M.DispOrd
-- Note : Check the Syntax bcos I don't have SQL server in my machine :-)
regards
gvprabu