CODEWITHSUNDEEP
sqlsql-serverformulaaggregate-functions
smallnorthumbrianvillage
smallnorthumbrianvillage

Reputation: 59

Compare value in each row to average of the column (SQL)

I am trying to create a view where the score for each row is compared to the average for that column, so that I can easily identify records by their rough "grade". Simplified code:

select recordID,
case when table.ColumnA>avg(all table.ColumnA) then 'Hard' else 'Easy' end as Difficulty,
from table
group by recordID, ColumnA

I've tried various combinations of this and the case formula keeps defaulting to 'else', which on investigation seems to be that every calculated value is coming out as 0, as both the row value and average value are being deemed the same.

I have a feeling the answer has something to do with Rollup, either on this table or the source table, but the syntax required is beyond me.

Anyone?

Upvotes: 1

Views: 499

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

You want a window function:

select recordID,
       (case when table.ColumnA > avg(table.ColumnA) over ()
             then 'Hard' else 'Easy'
        end) as Difficulty
from table;

Upvotes: 1

Related Questions