CODEWITHSUNDEEP
sqlsql-servergroup-byrollup
Richard B
Richard B

Reputation: 935

Using Group By and Rollup to Create Period to Date Summaries

I have a table in my database (SQL Server 2016) that contains the measurement error for a process I am running. Samples are taken at 10 minute intervals so the data looks something like:

Timestamp                  Error

'18 Oct 2019 14:00:00',    0.200
'18 Oct 2019 14:10:00',  - 0.175
'18 Oct 2019 14:20:00',  - 0.150
'18 Oct 2019 14:30:00',    0.183

I can easily use group by with rollup to summarise this data by month, week, day etc. But in doing so I'll get the summary for all days, weeks, months.

How would I write my query to show "To Date" summaries, i.e.

Average Error Over Period   Error

Today                        0.175
This Week                   -0.002
This Month                   0.201
This Year                    0.053
All Time                     0.027

The query to calculate the errors is fairly heavy so I'd rather not run it a number of times

Upvotes: 1

Views: 201

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Normally, I would do this as separate columns:

select avg(error) as total,
       avg(case when timestamp > cast(getdate() as date) then error end) as today,
       avg(case when timestamp > dateadd(day, -6, cast(getdate() as date) then error end) as this_week,
       . . .
from t;

I am not sure what your exact definitions are for "today", "this week", and so on. The above is an example of the conditional aggregation.

This goes through t only once.

If you want this in separate rows, you can unpivot the data. My preferred method uses cross apply:

with t as (
      select avg(error) as total,
             avg(case when timestamp > cast(getdate() as date) then error end) as today,
             avg(case when timestamp > dateadd(day, -6, cast(getdate() as date) then error end) as this_week,
           . . .
      from t
     )
select v.*
from t cross apply
     (values ('Total', total), ('Today', today), ('This week', this_week), . . .
     ) v(period, error);

Upvotes: 1

Related Questions