So what I need: I have code where I have defined Actuals, Forecast and Absolute Error for each title in each location (store).

Right now I need to

1. Sum all Absolute Errors for one specific location (store) like 30 Walmarts should have the same store Absolute Errors, Meijer should have their.

2. Sum all Actuals for one specific location (store) like 30 Walmarts should have the same store Actuals, Meijer should have their.

After they are calculated I need to

3. Divide sum of 'Abs Error' of all units for each location / sum of 'Actuals' for each location.

Does someone know if I can do this calculation over here in one SQL statement?

My current code

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

SELECT 
    b.[LOC], b.[DMDUNIT], 
    SUM(b.HistoryQuantity) AS 'Actuals', 
    SUM(a.TOTFCST) AS 'Forecast',
    SUM(ABS(b.HistoryQuantity - a.TOTFCST)) AS 'Abs Error'   
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]
ORDER BY 
    b.[LOC], b.[DMDUNIT]

How it looks now

CodePudding user response：

Try removing DMDUNIT from your GROUP BY clause.

SELECT
   b.[LOC]/*, b.[DMDUNIT]*/, SUM(b.HistoryQuantity) AS 'Actuals', SUM(a.TOTFCST) AS 'Forecast'
   ,SUM(ABS(b.HistoryQuantity - a.TOTFCST)) AS 'Abs Error', SUM(ABS(b.HistoryQuantity - a.TOTFCST))/SUM(b.HistoryQuantity) 'Abs Error/Actuals'

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]*/

ORDER BY

   b.[LOC]/*, b.[DMDUNIT]*/