Click here to Skip to main content
15,917,005 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi this is my output format:
---------------------------------------------------------------------------------------
CLASS 1A
TOTAL NO OF STUDENTS 52
STUDENTS PURCHASED 26
REMAINING STUDENTS 26


1)Here 'student_table' contain student id and class.
2) 'purchase_table' contain student id those who are purchase the dress.

now i want the result in above format (how many students are belongs to Class '1A' and count of purchased students and not purchased students).

i use this query but i get only not purchase students result.

select count(studentid) from student_table where studentid not exists (select studentid from 'purchase_table')


anybody help to solve this problem.
Posted
Comments
Mayur Panchal 7-May-13 7:29am    
Do you want result for only one class?
Raja Soosai 7-May-13 7:34am    
provide complete table structure

you have not provide table structure but for hint,...
SQL
Select Class_Nm, Sum(TotalStudents) TotalStudents, sum(Pur_students) Pur_students,Sum(TotalStudents)-sum(Pur_students) as Not_Pur_Student
From
(
    select Class_Nm,sum(Student_Id) as TotalStudents,0 as Pur_students 
    from student_table 
    where Class_Nm='1-A' group by Class_Nm
    
    Union all
    
    select Class_Nm,0 as TotalStudents,sum(Student_Id) as Pur_students 
    from student_table 
    where Class_Nm='1-A' and Student_Id not in (select studentid from purchase_table)
    group by Class_Nm
) as a
group by Class_Nm

Happy Coding!
:)
 
Share this answer
 
Comments
gvprabu 7-May-13 8:06am    
nice but he need each class 4 rows details, like he mentioned.
SQL
SELECT class,
       COUNT(*) total,
       COUNT(p.studentid) purchased,
       COUNT(*) - COUNT(p.studentid) remaining
  FROM student_table s LEFT JOIN
       purchase_table p ON s.studentid = p.studentid
 WHERE s.class = '1A'
GROUP BY class;


its working........thanks to all....
 
Share this answer
 
Comments
gvprabu 7-May-13 8:05am    
it will come in column wise right....you need row wise data or like this
Hi Check the following Query... try like this.
SQL
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900