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
Who is Participating?
SvenConnect With a Mentor Tech Lead Web-DevelopmentCommented:
(select count(numberofcontracts) from table where costumernumber = 'something') AS amountCon,
(select count(status) from table where costumernumber = 'something' and status = "accepted") AS acceptedCon,
(select count(status) from table where costumernumber = 'something' and status = "notaccepted") AS notAcceptedCon,
(select count(progress) from table where costumernumber = 'something' and progress = "development") AS  DevelopmentCon
Do you need to return the values all at once ?
You can use a union, something like this:
select 'All', count(numberofcontracts) AS amountCon
select 'Accepted',  count(status) AS amountCon where status = "accepted"
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,

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.