Select Count Help - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Select Count Help

Author

Topic

ahjeck
Starting Member

6 Posts

Posted - 2008-02-04 : 21:15:26

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-02-04 : 23:38:10

This probably belongs in the "New to SQL Server" forum, but try this...

SELECT A.SchoolName
FROM SchoolTable A
WHERE NOT EXISTS(SELECT * FROM SchoolTable WHERE SchoolName = A.SchoolName AND Admission IS NOT NULL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 00:12:50

or:-

SELECT st1.SchoolName
FROM SchoolTable st1
INNER JOIN (SELECT SchoolName,SUM(CASE WHEN Admission IS NULL 1 ELSE 0 END) AS NullCount,COUNT(*) AS TotalCount
FROM SchoolTable GROUP BY SchoolName)st2
ON st2.SchoolName=st1.SchoolName
WHERE st2.NullCount=st2.TotalCount

ahjeck
Starting Member

6 Posts

Posted - 2008-02-05 : 15:30:38

These work great. Thanks visakh16 and tprupsis.

Now I need to learn how to create these complex sql statements. :)

ahjeck
Starting Member

6 Posts

Posted - 2008-02-05 : 19:08:34

It seems I'm having trouble trying to get my desired results when modifying the scenario a bit.

The scenario now is basically the same, but with 2 more columns to check.

SchoolTable:

SchoolName | AppName | Admission | Enrollment | Grades
-------------------------------------------------------------
North School | Student1| Admitted | Enrolled |
North School | Student2| Admitted | |
North School | Student3| Admitted |
East School | Student4|
East School | Student5|
East School | Student6|
West School | Student7| Admitted
West School | Student8|

Results:
SchoolName
------------
East School

When I tried to update the above, which appears to make sense (to me), it doesn't work. Here's what I got (I used visakh16's example):

SELECT A.SchoolName
FROM SchoolTable A
WHERE NOT EXISTS(SELECT * FROM SchoolTable WHERE SchoolName = A.SchoolName AND Admission IS NOT NULL AND Enrollment IS NOT NULL AND Grades IS NOT NULL)

This isn't working because it seems to still list schools with values in them UNTIL at least one of the columns (of all records of that school) is full, such as 'North School' above. Anything I'm missing? Thanks..

ahjeck
Starting Member

6 Posts

Posted - 2008-02-05 : 21:14:13

After careful thought, I finally got it.

SELECT A.SchoolName
FROM SchoolTable A
WHERE NOT EXISTS(SELECT * FROM SchoolTable WHERE SchoolName = A.SchoolName AND (Admission IS NOT NULL OR Enrollment IS NOT NULL OR Grades IS NOT NULL))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources