Multiple count results in Query with different WHERE conditions.

Posted on 2007-08-09
Last Modified: 2013-11-05
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
Question by:Kripos56
    LVL 53

    Expert Comment

    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"
    LVL 11

    Accepted Solution

    (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
    LVL 58

    Expert Comment

    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,


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now