具有聚合函数的SQL GROUP BY CASE语句 [英] SQL GROUP BY CASE statement with aggregate function

问题描述

  CASE 
当col1> col2 THEN SUM（col3 * col4）
 ELSE 0 
 END as some_product 
  

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

解决方案

我的猜测是您并不真正想要 GROUP BY some_product。

答案：是否有一种方法可以为 GROUP BY 列别名例如在这种情况下的some_product，还是我需要把它放在子查询和组中？ 是：您不能 GROUP BY 列别名。 $ b $ SELECT 子句，其中列别名被分配，直到 GROUP BY 子句。内联视图或公用表表达式（CTE）可用于将结果用于分组。

$ b

b

  select ... 
 from（select ...，CASE WHEN col1> col2 THEN SUM（col3 * col4）ELSE 0 END as some_product 
从... 
 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 ... 
  

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?

解决方案

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.

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.

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

这篇关于具有聚合函数的SQL GROUP BY CASE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！