asked
How to use a Case Stmt along with AVG Function
I am having some trouble with using a select query using case and avg aggregate function. Below is what I have. If the field listed is between this certain range, I want to average those values else don't average if condition NOT met. Below is giving error about not in select list or not contained in the aggregate. Thanks! Select Distinct CASE WHEN cast(PREMIER_V1_2_BCC5030 as BIGINT) BETWEEN 0 and 999999990 THEN Avg(cast(PREMIER_V1_2_BCC5030 as BIGINT)) ELSE PREMIER_V1_2_BCC5030 END as Avg1 From MASTER_TABLE
I think I understand what you want - you want to average the values if they are between the range but show outliers as individual values? so in this table declare @MASTER_TABLE table (PREMIER_V1_2_BCC5030 int) insert into @MASTER_TABLE select 10; insert into @MASTER_TABLE select 0; insert into @MASTER_TABLE select 999599990; insert into @MASTER_TABLE select 999499990; insert into @MASTER_TABLE select 99939990; insert into @MASTER_TABLE select 999999992; insert into @MASTER_TABLE select 999999999; you would want to average the first 5 values, but show the last 2 separately? with cte_firstpass as ( select case when PREMIER_V1_2_BCC5030 BETWEEN 0 and 999999990 then -1 else PREMIER_V1_2_BCC5030 end as Avg_or_not, PREMIER_V1_2_BCC5030 from @MASTER_TABLE ) select distinct avg(PREMIER_V1_2_BCC5030)over(partition by Avg_or_not) from cte_firstpass gives the result ----------- 419807996 999999992 999999999 (3 row(s) affected) Is that what you mean? (this wouldn't handle 2 outlier values that are the same, it would aggregate them up to the average - is that required?)
Given the example data in my answer what do you want to see as the output?
This request has changed a little. Is there a way to add a field to the table "AVG_Score" and populate that field with the average if it passes case statement and if it doesn't populate it with the actual value. I know you can't use aggregate in an update statement. The I can run a count on the "AVG_Score" field like normal. Thanks!
17 People are following this question.
