Show all count numbers for all values 2

[toyt78]

I am trying to build an Access 2000 query where it gives me a count of all data values. My current query works where it gives me a count of what data values are in the database but need it to also show a count of 0 for data values that are not in database.

My values that can be entered in my database field (called rating) are:

1
2
3
4

If values 1 and 3 were in the database and values 2 and 4 were not, then my query results should show this:

Exp1000     MyCount
1            18
2            0
3            23
4            0

My attempts:

SELECT rating,count(*) AS MyCount
FROM myTable
GROUP BY [rating];

Also tried this and still didnt work:

SELECT iif((rating > 0),rating,'0'), count(*) AS MyCount
FROM myTable
GROUP BY [rating];

Results of my failed attempts:

Exp1000     MyCount
1            18
3            23

I need it to also show values 2 and 4 where the count is 0 but cant figure out how to do this:

Exp1000     MyCount
1            18
2            0
3            23
4            0

Please advise.

 

[Golom]

The key to your problem is the phrase "... not in the database ...". There is an infinity of numbers that are not in the database. You need some way to portray the numbers that are legitimate values for "Exp1000" ... in the database or not.

Just as an example, construct a table like this
[tt]
tblValidRatings
Rating
1
2
3
4
5
[/tt]
Then

SELECT V.Rating, Count(*) AS MyCount
FROM tblValidRatings As V LEFT JOIN myTable As M
     ON V.Rating = M.Rating
GROUP BY V.Rating;

 

[PHV]

Create a table with all the ratings and use an outer join for the stats:
SELECT A.rating, Sum(IIf(IsNull(B.rating),0,1)) AS MyCount
FROM tblRating A LEFT JOIN myTable B ON A.rating = B.rating
GROUP BY A.rating;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[toyt78]

Thanks, PHV works great but just for more information that I can learn from: the other one with Golom gives me back a count of 1 where the count should be 0. Any reason why?

Also I want to understand what PHV is doing here:

Sum(IIf(IsNull(B.rating),0,1))

If the Sum(total) of B.rating is Null then put the sum as 0 else put it as 1??? Please explain if possible.

Thanks!

 

[PHV]

Sum(IIf(IsNull(B.rating),0,1))
Calculate the number of times a B.rating is returned with all null counting as none.
Golom's solution count any non existent B.rating as 1 due the left join returning at least one row for each A.rating.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

PHV's solution is the correct one.

Guess I type faster than I think. Sorry to have mislead you.

 

[toyt78]

Thanks!
Still not sure what the 0,1 is doing in your SQL?
Sum(IIf(IsNull(B.rating),0,1))

 

[Golom]

The construct

Sum(IIf(IsNull(B.rating),0,1))

Contributes a value to the SUM being computed for each of the returned records.

If the value for B.Rating exists (i.e. it is NOT NULL) then a "1" is added to the sum. If it does not exist (i.e. it IS NULL) then 0 is added to the sum.

In a simple example, If myTable contained
[tt]
Rating
1
1
1
3
3
5
5
5
[/tt]

and tblRating contained 1, 2, 3, 4, 5 then before grouping
[tt]
Rating Add To SUM
1 1
1 1
1 1
2 0 (because mtTable.Rating IS NULL)
3 1
3 1
4 0 (because mtTable.Rating IS NULL)
5 1
5 1
5 1
[/tt]
Then taking the sums for each rating
[tt]
Rating Add To SUM
1 3
2 0
3 2
4 0
5 3
[/tt]

 

[toyt78]

Thanks for the explanation and all your help!