CODEWITHSUNDEEP
sqlpostgresql
Daryn
Daryn

Reputation: 1609

Postgres complicated sql query

Have a bit of a complicated query I am trying to work out. And I'm not really sure how to query google on it to get some help...

I have a table something like this:

 Date      | period | meter   | value
2017-01-01 |    1   | meter01 | 1.12
2017-01-01 |    2   | meter01 | 2.12
.
.
2017-01-01 |    1   | meter02 | 0.12
2017-01-01 |    2   | meter02 | 7.12
.
.
2017-01-02 |    1   | meter01 | 2.12
2017-01-02 |    2   | meter01 | 4.12
.
.

There are 48 periods for any given date. There could be hundreds of meters for any given date also.

I wish to return a result that looks something like this:

 Date       |  Meter    |  Period 1 | Period 2 | Period 3 | Period 4
2017-01-01  |  meter01  |    1.24   |   2.12      
2017-01-01  |  meter02  |    2.24   |   3.12

The result would give me the totals for each meter, date and period where the period columns are the sum of all readings for the given meter, period and date.

Upvotes: 0

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

One method is conditional aggregation:

select date, meter,
       sum(case when period = 1 then value end) as period_01,
       sum(case when period = 2 then value end) as period_02,
       sum(case when period = 3 then value end) as period_03,
       sum(case when period = 4 then value end) as period_04
from t
group by date, meter
order by date, meter;

Upvotes: 1

Related Questions