CODEWITHSUNDEEP
sqlpostgresqldatabase-designvariance
Jack Pollock
Jack Pollock

Reputation: 334

Work out variance of groups of rows in SQL

I'm looking to work out a variance value per month for a table of data, with each month containing three rows to be accounted for. I'm struggling to think of a way of doing this without 'looping' which, as far as I'm aware, isn't supported in SQL.

Here is an example table of what I mean:

+======================+=======+
| timestamp            | value |
+======================+=======+
| 2020-01-04T10:58:24Z | 10    | # January (Sum of vals = 110)
+----------------------+-------+
| 2020-01-14T10:58:21Z | 68    |
+----------------------+-------+
| 2020-01-29T10:58:12Z | 32    |
+----------------------+-------+
| 2020-02-04T10:58:13Z | 19    | # February (Sum of vals = 112)
+----------------------+-------+
| 2020-02-14T10:58:19Z | 5     |
+----------------------+-------+
| 2020-02-24T10:58:11Z | 88    |
+----------------------+-------+
| 2020-03-04T10:58:11Z | 72    | # March (Sum of vals = 184)
+----------------------+-------+
| 2020-03-15T10:58:10Z | 90    |
+----------------------+-------+
| 2020-03-29T10:58:16Z | 22    |
+----------------------+-------+
| ....                 | ....  |
+======================+=======+

I need to build a query which can combine all 3 values from each item in each month, then work out the variation of the combined value across months. Hopefully this makes sense? So in this case, I would need to work out the variance betweeen January (110), February (112) and March (184).

Does anyone have any suggestions as to how I could accomplish this? I'm using PostgreSQL, but need a vanilla SQL solution :/

Thanks!

Upvotes: 1

Views: 700

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Are you looking for aggregation by month and then a variance calculation? If so:

select variance(sum_vals)
from (select date_trunc('month', timestamp) as mon, sum(val) as sum_vals
      from t
      group by mon
     ) t;

Upvotes: 2

Related Questions