I have some data which looks like :

How to write a SQL query to find a average of a class score?

Class score : (100 * Time) / max(Time of that class) [kind of a % but instead of total time of that class we will use max time of that class.]

Expected result is :

for 1 : avg( (12 * 100 / max(12, 14, 56)), (14 * 100 / max(12, 14, 56)), (56 * 100 / max(12, 14, 56)))

same for all values

Thanks in advance

CodePudding user response：

If you look closely in your math formula, you will notice that it can be simplified a lot. So this simple query will give same result

SELECT class, SUM(time*100)/(MAX(time)*COUNT(*))
FROM *table_name*
GROUP BY class;

CodePudding user response：

Using subquery like this SELECT MAX(time) FROM test1 as b WHERE a.class = b.class help you to find max value of each class group. In this example I suppose table test1 as your table whitch contains time and class columns. test1 used two times in this query with a and b alias. special_avg column is your calculated value:

SELECT *, 
    (100 * time / (SELECT MAX(time) FROM test1 as b WHERE a.class = b.class)) as special_avg    
FROM test1

CodePudding user response：

Table and data

create table classes(Class int,Time int);

insert into classes values(1,12),(1,14),(2,3),(1,56),(3,4),(5,32);

select Class,(100*Time)/max(Time) over(PARTITION BY Class) from classes;

results ....

 class | ?column?
------- ----------
     1 |       21
     1 |       25
     1 |      100
     2 |      100
     3 |      100
     5 |      100