More Count Questions

[Amin]

So this seems like a tricky one. OK, so let's say you have two fields:
[Name], [Every dinner eaten]. So if they go out to Mcdonalds, the record is
Mcdonalds, but if they stay home, the record is null. So for example, let's
say the table looks like this:

Ted Olive Garden
Ted McDonalds
Ted
Ted McDonalds
Ted Olive Garden
Ted
Bill McDonalds
Bill Olive Garden
Bill


I can get my query to produce this

Ted Olive Garden 2
McDonalds 2
0

Bill Olive Garden 2
McDonalds 1
0


But how do I get it to count the Null fields? It always gives me zero.

This is my code:
SELECT [Name], [Store], COUNT[Store] GROUP BY [name]

Any ideas would be much appreciated.

Thanks
Amin

 

[BobT]

Null can be confusing, the answer is simple. Here's the query you need:

SELECT Count(1) - Count([Every Dinner Eaten]) as Home
FROM MyData

Basically this reads as - take the total number of records and subtract the
number of records where my [Every Dinner Eaten] field is not null.

Bob Tulk
MOUS (XP/97)

 

[Jerry Whittle]

SELECT [Name],
NZ([Store], "home") AS Stores,
COUNT(NZ([Store], "home")) AS TheStoreCount
FROM YourTableName
GROUP BY [name], NZ([Store], "home") ;

The table name was missing in your SQL statement so make sure that it's
correct in the statement above.

 

[Ken Sheridan]

Amin:

To count all rows rather than rows with a value in a certain column you can
use an asterisk instead of the column name:

SELECT [Name], [Store],
COUNT(*) As TimesUsed
FROM [YourTable]
GROUP BY [Name], [Store];

Ken Sheridan
Stafford, England