"column XYZ must be in group by" --> "cannot group by aggregate column"

i have trouble with following sql-statement (tri开发者_运维知识库mmed):

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.

My previous answer was incorrect - you cannot use HAVING in this scenario

You just need to put in the where clause:

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