具有聚合函数的SQL分组语句

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_product in this case, or do I need to put this in a subquery and group on that?

我想把它放到GROUP BY子句中，但这似乎会导致问题因为列中有一个聚合函数。是否有一种方法可以通过列别名(如在本例中为some_product)进行分组，还是需要将其放入子查询并对其进行分组?

3 个解决方案

#1

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

我的猜测是，您并不是真的想按某个产品进行分组。

The answer to: "Is there a way to GROUP BY a 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 BY a column alias.

答案是:“在这种情况下，是否有一种方法可以通过列别名(例如some_product)进行分组，或者我是否需要将其放入子查询并对其进行分组?”

The SELECT clause, where column aliases are assigned, is not processed until after the GROUP BY clause. 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:

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 ...

#2

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

虽然香农的回答在技术上是正确的，但看起来有点过头了。

The simple solution is that you need to put your summation outside of the case statement. 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).

基本上，您的旧代码告诉SQL为每一行分别执行sum(X*Y)(让每一行都有自己的答案，不能分组)。

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

我写的代码行接受和乘积，这就是你想要的。

#3

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 By the internal expression, (col3*col4) then

如果，otoh，您希望按内部表达式(col3*col4)进行分组

write the group By to match the expression w/o the SUM...

把这个组写成w/o的形式。

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

#1