asked on

Multiple count results in Query with different WHERE conditions.

Hi there..

The language is c# but this question is query specific though..

Ok.. i'm creating a query which will return some statistics, and what's special in this case.. is that one count must include all the records, while another one.. will only count for a specific set of records.. i'll describe with kvasi-sql here :

PS ! the --> (where xxxxx = "xxx") <-- is explaining the where situation i need pr count.

select count(numberofcontracts) AS amountCon,
count(status) AS acceptedCon--> (where status = "accepted") <--,
count(status) AS notAccepted --> (where status = "notaccepted") <--,
count (progress) --> (where progress = "development") <--
from table where costumernumber = 'something'.

Hope i explained me well enough :)

Best regards
Terje

Dirk Haest

Do you need to return the values all at once ?
You can use a union, something like this:
select 'All', count(numberofcontracts) AS amountCon
UNION
select 'Accepted', count(status) AS amountCon where status = "accepted"
...

ASKER CERTIFIED SOLUTION

Sven

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Markus Fischer

You can also sum boolean values.

select count(numberofcontracts) AS amountCon,
sum(status = 'accepted') AS acceptedCon,
sum(status = 'notaccepted') AS notAccepted,
sum(progress = 'development')
from table where costumernumber = 'something'

Depending on the coding for True (it's -1 in JetSQL, for example), you'll need to add a minus sign or use abs():

-sum(status = 'accepted') AS acceptedCon,
abs(sum(status = 'accepted')) AS acceptedCon,

Cheers!
(°v°)