15,791,091 members
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
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

## Solution 1

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

gvprabu 7-May-13 8:06am
nice but he need each class 4 rows details, like he mentioned.

## Solution 2

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....

gvprabu 7-May-13 8:05am
it will come in column wise right....you need row wise data or like this

## Solution 3

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