“列XYZ必须由” - > “不能按聚合列分组” [英] "column XYZ must be in group by" --> "cannot group by aggregate column"

问题描述

  SELECT nr，
（CASE WHEN 
 SUM（vkdtab.amount * liter）<> 0 AND 
 jjjjmm BETWEEN 201001 and 201009 
 THEN SUM（net）/ SUM（vkdtab.amount * liter）
 ELSE 0 END）作为返回
 FROM标签
 GROUP BY 1,2,3 
  

它应该给我一个特定时间段内的数量/升的物品，但是我得到的错误是：列返回必须由

添加该列之后：无法按聚合列分组。

这是功能性的，没有时间表：

  CASE WHEN 
 SUM（vkdtab.amount * liter）<> 0 
 THEN SUM（净值）/ SUM（vkdtab.amount * liter）
 ELSE 0 END 
  

如何添加时间范围而不会出现错误？

解决方案

我会尝试将GROUP BY更改为 GROUP BY nr，jjjjmm ，因此它与SELECT子句中的未聚合列匹配。你可以是GROUP BY或SUM / MIN / COUNT等等。如果这是错误的，那么聚合是错误的，因为它会使用英文表达你的聚合测试失败

GROUP BY序号也是一个可怕的概念，应该被拍摄。

i have trouble with following sql-statement (trimmed):

SELECT nr,
    (CASE WHEN 
        SUM(vkdtab.amount*liter)<>0 AND 
        jjjjmm BETWEEN 201001 and 201009 
    THEN SUM(net)/SUM(vkdtab.amount*liter) 
    ELSE 0 END) as return
FROM tab
GROUP BY 1,2,3

It should give me the amount/liter of items in a special timeframe, but I get the error: column return must be in group by

After I add that column: cannot group by aggregate column.

This is functional, just without the timeframe:

CASE WHEN 
    SUM(vkdtab.amount*liter)<>0
THEN SUM(net)/SUM(vkdtab.amount*liter) 
ELSE 0 END

How can I add the timeframe without getting an error?

解决方案

I'd try changing the GROUP BY to GROUP BY nr, jjjjmm so it matches your unaggregated columns in the SELECT clause. You either GROUP BY or SUM/MIN/COUNT etc

If this is wrong, then the aggregate is wrong because it would fail the "express your aggregate in plain english" test

GROUP BY ordinal is a vile concept too and should be shot.

这篇关于“列XYZ必须由” - > “不能按聚合列分组”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！