SQL Count Query Problem - 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

General SQL Server Forums

New to SQL Server Programming

SQL Count Query Problem

Author

Topic

hurdy
Starting Member

17 Posts

Posted - 2010-02-22 : 17:31:51

Hi everyone, thank you for the time taken out to read this.

I'm having trouble with a SQL query where I want to count through a column of boolean "check boxes" to determine the number of NULL values that exists and then group the number of NULL values depending on the ID that they belong to.

Let me show with a little more details.

There are two Tables "UnitIdentity" and "FaultsAndShortages

UnitIdentity table contains the following columns with respective data:

UnitID, SerialNo
1 , 051111111
2 , 051222222
3 , 051333333

FaultsAndShortages table contains the following columns with respective date:

FaultID, UnitID, Complete
1 , 1 , NULL
2 , 2 , TRUE
3 , 1 , TRUE
4 , 3 , TRUE
5 , 1 , NULL
6 , 2 , NULL
7 , 2 , TRUE

What I want to achieve is the following result:

SerialNo, NumberOfNulls
051111111 , 2
051222222 , 1
051333333 , 0

I have the following SQL Query:

SELECT UnitIdentity.SerialNo, ISNULL(SubTotal.Total, 0) AS NumberOfNulls
FROM UnitIdentity LEFT OUTER JOIN
     (SELECT        UnitID, COUNT(Complete) AS Total
      FROM            FaultsAndShortages
      WHERE        (Complete IS NULL)
      GROUP BY UnitID) AS SubTotal ON UnitIdentity.UnitID = SubTotal.UnitID

But this query only returns the following:

SerialNo, NumberOfNulls
051111111 , 0
051222222 , 0
051333333 , 0

But, if I change the

WHERE (Complete IS NULL)

statement to

WHERE (Complete = 1)

I get the following which is correct but you could say it's the inverse to what I want.

SerialNo, NumberOfNulls
051111111 , 1
051222222 , 2
051333333 , 1

Does anyone have any suggestions on how to solve this problem of mine please?

Thank you again for your times.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 18:04:41

Replace COUNT(Complete) with COUNT(*)