Assistance with PERCENTILE_CONT function and GROUP By error-CodePudding

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

CodePudding user response：

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