Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql query to COUNT two different values

Author  Topic 

adityasirohi
Starting Member

3 Posts

Posted - 2013-06-20 : 00:21:44
Hi All,

I am new to SQL and this forum,
I have a STUDENTS table which has a column GRADES, STUDENT_ID. I want to display the number of students that received a GRADE of 3 or 4. The total number of students that received a GRADE of 3 should be separate from the total number of students that received a GRADE of 4.

This is what i have:
SELECT COUNT(STUDENT_ID) AS GRADE3, COUNT(STUDENT_ID) AS GRADE4 from STUDENTS
where GRADE=3
or GRADE=4;

This is not working. Can anyone suggest?

leearenaa
Starting Member

3 Posts

Posted - 2013-06-20 : 00:37:31
What you can do is you need to separate the filtering and union the table.
below is the suggestion

SELECT COUNT(STUDENT_ID) AS GRADE3, Grades from STUDENTS
where GRADE=3
Group by Grades
UNION
SELECT COUNT(STUDENT_ID) AS GRADE3, Grades from STUDENTS
where GRADE=4
Group by Grades

LeeAreNaa
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 00:40:28
[code]
SELECT COUNT(CASE WHEN GRADE = 3 THEN STUDENT_ID END) AS GRADE3,
COUNT(CASE WHEN GRADE=4 THEN STUDENT_ID END) AS GRADE4
from STUDENTS
where GRADE IN (3,4)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

adityasirohi
Starting Member

3 Posts

Posted - 2013-06-20 : 00:51:36
Thanks!!
quote:
Originally posted by leearenaa

What you can do is you need to separate the filtering and union the table.
below is the suggestion

SELECT COUNT(STUDENT_ID) AS GRADE3, Grades from STUDENTS
where GRADE=3
Group by Grades
UNION
SELECT COUNT(STUDENT_ID) AS GRADE3, Grades from STUDENTS
where GRADE=4
Group by Grades

LeeAreNaa

Go to Top of Page
   

- Advertisement -