How to get three count values from same column using SQL in Access?

Question

I have a table that has an integer column from which I am trying to get a few counts from. Basically I need four separate counts from the same column. The first value I need returned is the count of how many records have an integer value stored in this column between two values such as 213 and 9999, including the min and max values. The other three count values I need returned are just the count of records between different values of this column. I've tried doing queries like...

SELECT (SELECT Count(ID) FROM view1 WHERE ((MyIntColumn BETWEEN 213 AND 9999));)
AS Value1, (SELECT Count(ID) FROM FROM view1 WHERE ((MyIntColumn BETWEEN 500 AND 600));) AS Value2 FROM view1; 

So there are for example, ten records with this column value between 213 and 9999. The result returned from this query gives me 10, but it gives me the same value of 10, 618 times which is the number of total records in the table. How would it be possible for me to only have it return one record of 10 instead?

Answer 1

Use the Iif() function instead of CASE WHEN

     select  Condition1: iif( ),   condition2: iif(  ),  etc

P.S. : What I used to do when working with Access was have the iif() resolve to 1 or 0 and then do a SUM() to get the counts. Roundabout but it worked better with aggregation since it avoided nulls.

Answer 2

SELECT
COUNT(CASE 
     WHEN MyIntColumn >= 213 AND MyIntColumn <= 9999 
     THEN MyIntColumn 
     ELSE NULL 
     END) AS FirstValue
, ??? AS SecondValue
, ??? AS ThirdValue
, ??? AS FourthValue
FROM Table

This doesn't need nesting or CTE or anything. Just define via CASE your condition within COUNTs argument.

I dont really understand what You want in the second, third an fourth column. Sounds to me, its very similar to the first one.

Answer 3

Reformatted, your query looks like:

SELECT  (
        SELECT  Count(ID) 
        FROM    view1 
        WHERE   MyIntColumn BETWEEN 213 AND 9999
        ) AS Value1 
FROM    view1; 

So you are selecting a subquery expression that is not related to the outer query. For each row in view1, you calculate the number of rows in view1.

Instead, try to do the calculation once. You just have to remove your outer query:

SELECT  Count(ID) 
FROM    view1 
WHERE   MyIntColumn BETWEEN 213 AND 9999;

Answer 4

OLEDB Connection in MS Access does not support key words CASE and WHEN .

You can only use iif() function to count two, three.. values in same columns

SELECT Attendance.StudentName, Count(IIf([Attendance]![Yes_No]='Yes',1,Null)) AS Yes, Count(IIf([Attendance]![Yes_No]='No',1,Null)) AS [No], Count(IIf([Attendance]![Yes_No]='Not',1,Null)) AS [Not], Count(IIf([Attendance]![Yes_No],1,Null)) AS Total

FROM Attendance GROUP BY Attendance.StudentName;