CODEWITHSUNDEEP
sqlsql-servert-sql
CaptainCrypto
CaptainCrypto

Reputation: 164

T-SQL SUM All with a Conditional COUNT

I have a query that produces the following:

Team  | Member  |  Cancelled | Rate
-----------------------------------
  1     John         FALSE      150
  1     Bill         TRUE        10
  2     Sarah        FALSE      145
  2     James        FALSE      110
  2     Ashley       TRUE         0

What I need is to select the count of members for a team where cancelled is false and the sum of the rate regardless of cancelled status...something like this:

SELECT
    Team,
    COUNT(Member),    --WHERE Cancelled = FALSE
    SUM(Rate)         --All Rows
FROM
    [QUERY]
GROUP BY
    Team

So the result would look like this:

Team  |  CountOfMember | SumOfRate
----------------------------------
  1           1                160
  2           2                255

This is just an example. The real query has multiple complex joins. I know I could do one query for the sum of the rate and then another for the count and then join the results of those two together, but is there a simpler way that would be less taxing and not cause me to copy and paste an already complex query?

Upvotes: 1

Views: 6987

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You want a conditional sum, something like this:

sum(case when cancelled = 'false' then 1 else 0 end)

The reason for using sum(). The sum() is processing the records and adding a value, either 0 or 1 for every record. The value depends on the valued of cancelled. When it is false, then the sum() increments by 1 -- counting the number of such values.

You can do something similar with count(), like this:

count(case when cancelled = 'false' then cancelled end)

The trick here is that count() counts the number of non-NULL values. The then clause can be anything that is not NULL -- cancelled, the constant 1, or some other field. Without an else, any other value is turned into NULL and not counted.

I have always preferred the sum() version over the count() version, because I think it is more explicit. In other dialects of SQL, you can sometimes shorten it to:

sum(cancelled = 'false')

which, once you get used to it, makes a lot of sense.

Upvotes: 12

Related Questions