CODEWITHSUNDEEP
sqlgroup-byaggregatesum
Sven
Sven

Reputation: 2889

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

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?

Upvotes: 0

Views: 3219

Answers (2)

gbn
gbn

Reputation: 432667

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.

Upvotes: 2

codingbadger
codingbadger

Reputation: 44032

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

Upvotes: 0

Related Questions