SUM of all calculated fields looking at each individual criteria
-
I am sure that my question must be easy for someone with more SQL experience. It is extremely confusing for me at this point.
So what I need. I have a 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 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 code.
How it looks now
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]- This topic was modified 3 years ago by JeremyU.
-
Where's b.[DMDPostDate] is your SELECT statement, because it's in your GROUP BY clause?
-
@pietlinden just corrected thank you!!!. Do you know how i can calculate what i wrote?
- JeremyU wrote:
So what I need. I have a code where I have defined Actuals, Forecast and Absolute Error for each title in each location (store).
When you want to sum individual columns based on different criteria in the same query.
Give it a try with database analytical function, something along the lines SUM (AbsoluteError) OVER (Partition by Location Order by Location)
=======================================================================
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply