“列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屋!