T-SQL SUM All with Conditional COUNT

Question

I have a query that creates 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 

I need to choose the number of team members in which false is canceled, and the bet amount regardless of the canceled status ... something like this:

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

Thus, the result will look like this:

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

This is just an example. A real query has several complex joins. I know that I could make one request for the amount of the bet, and then another for the account, and then join the results of the two together, but is there an easier way that will be less taxed and not force me to copy and paste already complicated query?

Answer 1

You want a notional amount, something like this:

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

Reason for using sum() . sum() processes the records and adds a value, either 0 or 1 for each record. The value depends on the value cancelled . If false, then sum() incremented by 1 - counts the number of such values.

You can do something similar with count() , for example:

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

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

I always preferred the version of sum() over the version of count() , 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.