"XYZ column must be in group" & # 8594; "cannot be grouped by aggregate column"

Question

I have problems with the 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 number / liter of elements in a special timeframe, but I get an error: column return must be in group by

After you add this column: cannot group by aggregate column.

This is functional, just without a timeframe:

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

How can I add a timeframe without error?

Answer 1

I would try changing GROUP BY GROUP BY nr, jjjjmmto match your non-aggregated columns in a SELECT clause. You are either GROUP BY or SUM / MIN / COUNT, etc.

, , " "

GROUP BY ordinal , .

Answer 2

- HAVING

where:

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

Where jjjjmm BETWEEN 201001 and 201009
And jjjjmm BETWEEN 201013 and 201020

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