Count with Nulls - 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

Development Tools

ASP.NET

Count with Nulls

Author

Topic

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2004-08-02 : 10:17:49

I want to get an average of a column values by taking the sum of the column divided by the count; however, the column contains null values and I don't want those included into the count of the column. Here is my current code:

SELECT ROUND (SUM(SalonNeatAndClean)/Count(*)*100, 2),
ROUND (SUM(StaffNeatAndClean)/Count(*)*100, 2),
ROUND (SUM(FriendlyGreeting)/Count(*)*100, 2),
ROUND (SUM(StylistsBusy)/Count(*)*100, 2),
ROUND (SUM(StylistAtFrontDesk)/Count(*)*100, 2),
ROUND (SUM(SignedIn)/Count(SignedIn Is Not Null) *100, 2),
ROUND (SUM(Greeted)/Count(Greeted Is Not Null)*100, 2),
ROUND (SUM(ToldHowSignIn)/Count(ToldHowSignIn IS NOT NULL)*100, 2),
ROUND (SUM(WaitTime)/Count(WaitTime Is Not Null)*100, 2),
ROUND (SUM(HairShampooed)/Count(HairShampooed Is Not Null)*100, 2),
ROUND (SUM(ThankedWhenLeft)/Count(*)*100, 2),
ROUND (SUM(OfferedReceipt)/Count(*)*100, 2),
ROUND (SUM(LocatingSalon)/Count(*)*100, 2),
ROUND (SUM(ComeBack)/Count(*)*100, 2)
FROM Results
WHERE IIf(('::SalonId::'=''), True, SalonId = '::SalonId::')
AND IIf(('::State::'=''), True, State = '::State::')
AND IIf(('::ItemPurchased::'=''), True, ItemPurchased='::ItemPurchased::')
AND IIf(('::Gender::'=''), True, Gender= '::Gender::')
AND IIf(('::AgeRange::'=''), True, AgeRange= '::AgeRange::')

Everything works great on this code; however, on the columns of SignedIn, Greeted, ToldHowSignIn, WaitTime, and HairShampooed are all incorrect as it is taking the average of the records with null values...example:

SignedIn has 10 entries, 8 of which contain values. 4 yes's, 4 no's. It should be 50% (4/8 as no's contain value of 0, and yes's are value of 1)...however, it gives 40% as the answer (4/10)

any ideas?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-02 : 10:36:20

What RDMS is this? Access ?

instead of using COUNT(*), use COUNT(ColumnName) -- that should return the count of non-null values in a particular column.

- Jeff

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2004-08-02 : 10:50:25

Excellent!! That works great! Thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources