How not to aggregate on numeric fields-CodePudding

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]

CodePudding user response：

ABS() is not an aggregate function and cannot be used without, for example, passing aggregate values to it

use any like this

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