November 11, 2021 at 4:57 pm
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
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 11, 2021 at 6:43 pm
Phil thank you.
November 20, 2021 at 12:32 am
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