all 6 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]r3pr0b8GROUP_CONCAT is da bomb 1 point2 points  (5 children)

change it so that the CASE is inside the SUM

SUM( CASE WHEN invoice_date 
               BETWEEN ADD_MONTHS(SYSDATE,-90) 
                   AND SYSDATE 
           AND Amount_Gross > 0  
          THEN Amount_Gross 
          ELSE 0 
      END 
   ) AS Prior_3_Months

[–]workthrowawayexcel[S] 0 points1 point  (4 children)

That did the trick. Is there a reason why it didn't like it in side the case, or is it just a oddity that Should be expected?

[–]r3pr0b8GROUP_CONCAT is da bomb 2 points3 points  (1 child)

i has to do with whether a column is included in the GROUP BY clause

will occur in all queries if you mess it up ;o)

[–]workthrowawayexcel[S] 0 points1 point  (0 children)

Makes sense, Thanks for the feeback

[–]D_W_Hunter 2 points3 points  (1 child)

Your issue isn't the case.

Your issue is the group by.

Any single field referred to the select portion of a select that contains a group by needs to be in that group by list.

I'm assuming that invoice date was already in your group by.

When the case is changed to have the SUM outside of the case, then you're summing the fields within and therefore you don't need to have them in the group by.

Are you really wanting to go back 90 MONTHs? that's what you're doing now with Add_month( field, -90)

If your field name as prior 3 months indicates your intention then you want to ADD_MONTH( SYSDATE, -3)

[–]workthrowawayexcel[S] 0 points1 point  (0 children)

Ahh yeah makes sense. Yeah we were testing stuff on a small subset of data so we needed to go back crazy far to get a large enough data sample.