Skip to content
Advertisement

Assistance with PERCENTILE_CONT function and GROUP By error

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?

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.

DB Fiddle: Try it here

Minimum steps to reproduce the error:

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement