All,
I am having problems with the below query. I am trying to get stat data from our database for the last 3 years but I keep getting the error message:
***Column 'OC_VDATA.DATA1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.***
I know it has something to do with the DATA1 column but I am not familiar enough using the PERCENTILE_CONT function to know what the solution is.
Anyone have any ideas?
WITH Q AS ( SELECT stagingPLM.dbo.ITEM_CODES.ITEM_CODE, AVG(OC_VDATA.DATA1) AS Mean, STDEVP(OC_VDATA.DATA1) AS StandardDev, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY OC_VDATA.DATA1) OVER (PARTITION BY stagingPLM.dbo.ITEM_CODES.ITEM_CODE) AS Median FROM OC_VDATA INNER JOIN OC_VDAT_AUX ON OC_VDATA.PARTNO = OC_VDAT_AUX.PARTNOAUX AND OC_VDATA.DATETIME = OC_VDAT_AUX.DATETIMEAUX INNER JOIN stagingPLM.dbo.ITEM_CODES ON LEFT(OC_VDATA.PARTNO, 12) = stagingPLM.dbo.ITEM_CODES.SPEC_NO AND LEFT(OC_VDAT_AUX.PARTNOAUX, 12) = stagingPLM.dbo.ITEM_CODES.SPEC_NO WHERE (OC_VDAT_AUX.UDL28 LIKE '%PLASTIC%') AND (RIGHT(OC_VDATA.PARTNO, 6) = '036150') AND (CAST(OC_VDAT_AUX.UDL40 AS DATETIME) BETWEEN CONVERT(datetime, '2019-05-18 00:00:00', 102) AND CONVERT(datetime, '2022-05-18 00:00:00', 102)) GROUP BY stagingPLM.dbo.ITEM_CODES.ITEM_CODE ) SELECT * FROM Q
Advertisement
Answer
The error is because of the code WITHIN GROUP (ORDER BY OC_VDATA.DATA1)
.
You are doing GROUP BY(for AVG and STDEVP) based on ITEM_CODE
, whereas ORDER BY is there on OC_VDATA.DATA1
for the Window function.
Better to calculate AVG
,STDEVP
and PERCENTILE_CONT
with Window Function, instead of half through GROUP BY and half through Window Function.
By considering the minimum required columns to reproduce the issue, you can rewrite the query as below to get the desired output.
SELECT DISTINCT item_codes.item_code, Avg(oc_vdata.data1) over( PARTITION BY item_codes.item_code) AS Mean, Stdevp(oc_vdata.data1) over( PARTITION BY item_codes.item_code) AS StandardDev, Percentile_cont(0.5) within GROUP (ORDER BY oc_vdata.data1) over ( PARTITION BY item_codes.item_code) AS Median FROM oc_vdata inner join item_codes ON Left(oc_vdata.partno, 12) = item_codes.spec_no
DB Fiddle: Try it here
Minimum steps to reproduce the error:
SELECT item_codes.item_code, Avg(oc_vdata.data1) AS Mean, Stdevp(oc_vdata.data1) AS StandardDev FROM oc_vdata INNER JOIN item_codes ON LEFT(oc_vdata.partno, 12) = item_codes.spec_no GROUP BY item_codes.item_code ORDER BY oc_vdata.data1 -- This will cause the error