SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql query to COUNT two different values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adityasirohi
Starting Member

3 Posts

Posted - 06/20/2013 :  00:21:44  Show Profile  Reply with Quote
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?

Edited by - adityasirohi on 06/20/2013 00:22:49

leearenaa
Starting Member

Malaysia
3 Posts

Posted - 06/20/2013 :  00:37:31  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 06/20/2013 :  00:40:28  Show Profile  Reply with Quote

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)


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

adityasirohi
Starting Member

3 Posts

Posted - 06/20/2013 :  00:51:36  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000