带聚合函数的 SQL GROUP BY CASE 语句

SQL GROUP BY CASE statement with aggregate function

提问by Bryan Ward

I have a column that looks something like this:

我有一个看起来像这样的列：

CASE
    WHEN col1 > col2 THEN SUM(col3*col4)
    ELSE 0
END AS some_product

And I would like to put it in my GROUP BY clause, but this seems to cause problems because there is an aggregate function in column. Is there a way to GROUP BY a column alias such as some_productin this case, or do I need to put this in a subquery and group on that?

我想把它放在我的 GROUP BY 子句中，但这似乎会导致问题，因为列中有一个聚合函数。有没有办法 GROUP BY 列别名，例如some_product在这种情况下，还是我需要将它放在子查询中并对其进行分组？

回答by Shannon Severance

My guess is that you don't really want to GROUP BYsome_product.

我的猜测是你并不真的想要GROUP BYsome_product。

The answer to:"Is there a way to GROUP BYa column alias such as some_product in this case, or do I need to put this in a subquery and group on that?" is:You can not GROUP BYa column alias.

的回答：“有没有办法GROUP BY列别名，如在这种情况下some_product，或者我需要把这个在一个子查询和组吗？” 是：你不能GROUP BY有一个列别名。

The SELECTclause, where column aliases are assigned, is not processed until after the GROUP BYclause. An inline view or common table expression (CTE) could be used to make the results available for grouping.

的SELECT子句，其中列别名分配，不被处理，直到后GROUP BY子句。内联视图或公用表表达式 (CTE) 可用于使结果可用于分组。

Inline view:

内联视图：

select ...
from (select ... , CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END AS some_product
   from ...
   group by col1, col2 ... ) T
group by some_product ...

CTE:

CTE：

with T as (select ... , CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END AS some_product
   from ...
   group by col1, col2 ... )
select ...
from T
group by some_product ... 

回答by user2076246

While Shannon's answeris technically correct, it looks like overkill.

虽然香农的回答在技​​术上是正确的，但它看起来有点矫枉过正。

The simple solution is that you need to put your summation outside of the casestatement. This should do the trick:

简单的解决方案是您需要将求和放在case语句之外。这应该可以解决问题：

sum(CASE WHEN col1 > col2 THEN col3*col4 ELSE 0 END) AS some_product

Basically, your old code tells SQL to execute the sum(X*Y)for each line individually (leaving each line with its own answer that can't be grouped).

基本上，您的旧代码告诉 SQLsum(X*Y)单独执行每一行（让每一行都有自己无法分组的答案）。

The code line I have written takes the sum product, which is what you want.

我写的代码行采用 sum product，这就是你想要的。

回答by Charles Bretana

If you are grouping by some other value, then instead of what you have,

如果您按其他值分组，则不是您所拥有的，

write it as

写成

Sum(CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END) as SumSomeProduct

If, otoh, you want to group Bythe internal expression, (col3*col4)then

如果，otoh，你想要group By内部表达，(col3*col4)那么

write the group Byto match the expression w/o the SUM...

写group By表达式W /匹配○ SUM...

Select Sum(Case When col1 > col2 Then col3*col4 Else 0 End) as SumSomeProduct
From ...

Group By Case When col1 > col2 Then col3*col4 Else 0 End 

Finally, if you want to group By the actual aggregate

最后，如果要按实际聚合分组

Select SumSomeProduct, Count(*), <other aggregate functions>
From (Select <other columns you are grouping By>, 
      Sum(Case When col1 > col2 
          Then col3*col4 Else 0 End) as SumSomeProduct
      From Table
      Group By <Other Columns> ) As Z
Group by SumSomeProduct

回答by Matt

I think the answer is pretty simple (unless I'm missing something?)

我认为答案很简单（除非我遗漏了什么？）

SELECT    
CASE
    WHEN col1 > col2 THEN SUM(col3*col4)
    ELSE 0
END AS some_product
FROM some_table
GROUP BY
CASE
    WHEN col1 > col2 THEN SUM(col3*col4)
    ELSE 0
END

You can put the CASE STATEMENT in the GROUP BY verbatim (minus the alias column name)

您可以将 CASE STATEMENT 逐字放在 GROUP BY 中（减去别名列名）