[SQL] Dynamicly labeling users by score

Question

I've got a table with users and their score, a decimal number ranging 1 - 10. Table(user_id, score) I also have a 1 row query with the average of the scores (about 6), and the standard deviation (about 0.5). I use MS Access 2007.

I want to label the users A, B, C, D, where:

• A has a score higher than (avg+stdev);
• B has a score lower than A, but higher than the average;
• C has a score lower than the average, but higher than (avg-stdev)
• D has a score lower than (avg-stdev).

If I export all data to Excel, I can calculate the values easily, and import them back to the database. Obviously, this isn't the most elegant way. I would like to do this with SQL as a Query. The result should be a table (user_id, label)

But how?

Answer 1

You can use a cross join to join up your users to the 1-row stats query. Then you can use a nested iif to calculate the grade.

Something like this...

SELECT  users.*,grade.*
,iif(users.score>grade.high,"A",iif(users.score>grade.average,"B",iif(users.score>grade.low,"C","D"))) as label
FROM (SELECT round(avg(users.score)-stdev(users.score),1) as low
,round(avg(users.score),1) as average
,round(avg(users.score)+stdev(users.score),1) as high
FROM users)  AS grade, users;

Answer 2

The IIF did the trick.

I adopted the Query with the average scores to add the minimum A, B and C-scores

Table(avg,stdev,Ascore,Bscore,Cscore) as averages

The final query looked like

SELECT user.Id, user.avgScore, 
IIf(avgScore>averages.Ascore,"A",
 IIf(avgScore>averages.Bscore,"B",
  IIf(avgScore>averages.Cscore,"C","D"))) AS label
FROM averages, users