Post reply

How not to aggregate on numeric fields

  • November 11, 2021 at 4:57 pm

    #3952090

     

    Hey everyone,

    I have this code with which I am trying not to aggregate on two columns HistoryQuantity and TOTFCST. I keep getting this error

    Column 'SCPOMGR.HISTWIDE_CHAIN.HistoryQuantity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

    I don't think I should group by numeric columns.

    Is there any way around this?

    -- WAPE (chain)
    SET ARITHABORT OFF 
    SET ANSI_WARNINGS OFF

    SELECT 
        b.[LOC], b.[DMDUNIT], 
        ABS(b.HistoryQuantity - a.TOTFCST) AS 'Abs Error',    
        SUM(b.HistoryQuantity) AS 'Sum of Actuals',
        SUM(ABS((b.HistoryQuantity - a.TOTFCST)))  AS 'Sum of Abs Error', 
        SUM(ABS((b.HistoryQuantity - a.TOTFCST))) / SUM(b.HistoryQuantity) AS 'WAPE'
    FROM 
        SCPOMGR.FCSTPERFSTATIC a
    JOIN 
        SCPOMGR.HISTWIDE_CHAIN b ON a.[STARTDATE] = b.[DMDPostDate] 
                                 AND a.[DMDUNIT] = b.[DMDUNIT] 
                                 AND a.[LOC] = b.[LOC]
    GROUP BY 
        b.[LOC], b.[DMDUNIT], b.[DMDPostDate]
    ORDER BY 
        b.[LOC]

  • November 11, 2021 at 5:07 pm

    #3952100

    ABS is not an aggregate function & that is your problem, I think. If you changed that to

    SUM(ABS(b.HistoryQuantity - a.TOTFCST))

    it might resolve your issue.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • November 11, 2021 at 6:43 pm

    #3952132

    Phil thank you.

  • November 20, 2021 at 12:32 am

    #3955008

    If you are doing an aggregation every field must be either an aggregation type or included in the GROUP BY.

    If you don't want it aggregated then you will need to exclude it from the query and then bring in back in using a second query.  Alternatively you may find that SUM() OVER() provides a solution and is more readable than SUM().... GROUP BY

     

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply