Thread: Multiple Counts from the Same Table

I am looking to produce multiple counts for a table in one query.

Currently I can get the count to work for one condition

SELECT Count(Risks.[Appeal Status]) AS [0 - 5 Days]

FROM Risks INNER JOIN Customers ON Risks.cust_id = Customers.Cust_id

WHERE (((Risks.[Opened Date]) Between Date() And Date()-5) AND ((Risks.[5 Day])=No

Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));

What I would like to do is add another count for

WHERE (((Risks.[Opened Date]) Between Date()-6 And Date()-10) AND ((Risks.[5 Day])=No Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));

and return that into a column AS [5 - 10 Days] ( I have about 6 to run in total but understanding how to do two should point me in the right direction)

When I try to add this it assumes it as an AND operator and returns 0 for the two columns so obviously my second query is upsetting it.

I take it I need to nest the second query but I cant get the correct syntax...

again, any help would be greatly appreciated

Originally Posted by gatorator

I am looking to produce multiple counts for a table in one query.

Currently I can get the count to work for one condition

SELECT Count(Risks.[Appeal Status]) AS [0 - 5 Days]

FROM Risks INNER JOIN Customers ON Risks.cust_id = Customers.Cust_id

WHERE (((Risks.[Opened Date]) Between Date() And Date()-5) AND ((Risks.[5 Day])=No

Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));

What I would like to do is add another count for

WHERE (((Risks.[Opened Date]) Between Date()-6 And Date()-10) AND ((Risks.[5 Day])=No Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));

and return that into a column AS [5 - 10 Days] ( I have about 6 to run in total but understanding how to do two should point me in the right direction)

When I try to add this it assumes it as an AND operator and returns 0 for the two columns so obviously my second query is upsetting it.

I take it I need to nest the second query but I cant get the correct syntax...

again, any help would be greatly appreciated

Solved it myself, in case anyone is interested

SELECT Count(Risks.[Appeal Status]) AS [0 - 5 Days],

(Select Count(Risks.[Appeal Status]) AS [5 - 10 Days] FROM Risks INNER JOIN

Customers ON Risks.cust_id = Customers.Cust_id

WHERE (((Risks.[Opened Date]) Between Date()-5 And Date()-10) AND ((Risks.[5 Day])

=No Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"))) AS [5 - 10 Days]

FROM Risks INNER JOIN Customers ON Risks.cust_id=Customers.Cust_id

WHERE (((Risks.[Opened Date]) Between Date() And Date()-5) AND ((Risks.[5 Day])=No

Or (Risks.[5 Day])=Yes) AND ((Risks.[Appeal Status])="Open"));