CODEWITHSUNDEEP
sqloracle-databaseaggregate-functions
quento
quento

Reputation: 1114

SQL: Oracle avg over group

I would like to show my problem on example. So, I have students, they are studying in different schools and I would like to count average by schools and then count total average.

Example:

example

So, how could i get result like this one?

Thanks in advance!

One more option - it is really much more comfortable for me to do this in one query.

Upvotes: 1

Views: 5086

Answers (1)

user5683823
user5683823

Reputation: 

with
     marks ( school, student, mark ) as (
       select 'School 1', 'James' , 5 from dual union all
       select 'School 1', 'Mark'  , 4 from dual union all
       select 'School 1', 'Howard', 5 from dual union all
       select 'School 4', 'Jho'   , 4 from dual union all
       select 'School 5', 'Kate'  , 5 from dual
     ),
     a ( school, s_avg ) as (
       select   school, avg(mark) 
       from     marks
       group by school
     )
select school, student, mark,
       avg(mark) over (partition by school) as avg_by_school,
       (select avg(s_avg) from a) as global_avg
from   marks;


SCHOOL   STUDENT       MARK AVG_BY_SCHOOL GLOBAL_AVG
-------- ------- ---------- ------------- ----------
School 1 James            5         4.667      4.556
School 1 Mark             4         4.667      4.556
School 1 Howard           5         4.667      4.556
School 4 Jho              4         4.000      4.556
School 5 Kate             5         5.000      4.556

5 rows selected.

Upvotes: 3

Related Questions