T-SQL SUM All with a Conditional COUNT

T-SQL SUM All with a Conditional COUNT(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?

推荐答案

你想要一个条件总和，像这样:

You want a conditional sum, something like this:

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

使用sum()的原因.sum() 正在处理记录并为每条记录添加一个值，0 或 1.该值取决于cancelled 的值.当它为假时，sum() 增加 1 —— 计算这些值的数量.

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.

你可以用 count() 做类似的事情，像这样:

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

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

这里的技巧是 count() 计算非 NULL 值的数量.then 子句可以是任何非 NULL -- cancelled、常量 1 或其他字段.如果没有 else，任何其他值都会变成 NULL 并且不计算在内.

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.

我一直更喜欢 sum() 版本而不是 count() 版本，因为我认为它更明确.在 SQL 的其他方言中，您有时可以将其缩短为:

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.

这篇关于T-SQL SUM All with a Conditional COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！