CODEWITHSUNDEEP
sqlgoogle-bigquery
Vine
Vine

Reputation: 395

Adding additional group by in running average

I have the current code that is working

select format_date('%Y%m', date) as yyyymm,
       (sum(sum(val)) over (order by min(date)) / 
        sum(count(*)) over (order by min(date))
       ) as running_avg
from t
group by yyyymm
order by yyyymm;

Returns

yyyymm  Score
201712  25.57931742
201801  24.69794466
201802  24.23110781
201803  23.85651947
201804  23.66164799
201805  23.43029053
201806  23.17074628
201807  23.09766588
201808  23.08902284

I am now trying to add an additional group by clause, for department. The query runs however the results are inaccurate, can anyone recognize what i am doing incorrectly?

select format_date('%Y%m', date) as yyyymm, department
       (sum(sum(val)) over (order by min(date)) / 
        sum(count(*)) over (order by min(date))
       ) as running_avg
from t
group by yyyymm, department
order by yyyymm;

Returns

yyyymm  department  Score
201712  HR  6.704365079
201712  F&B 8.550338502
201712  Marketing   8.550338502
201712  I.T.    9.857502908
201712  Security    9.551491994
201712  Contractors 9.411654456
201712  Executive Office    9.637075283
201712  Property Services   9.45905826
201712  Corporate   9.57458477
201712  Legal   9.700320268

Upvotes: 1

Views: 87

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You need to add department to the partition by:

select department, format_date('%Y%m', date) as yyyymm,
       (sum(sum(val)) over (partition by department order by min(date)) / 
        sum(count(*)) over (partition by department order by min(date))
       ) as running_avg
from t
group by yyyymm, department
order by department, yyyymm;

Upvotes: 2

Related Questions