Postgres complicated sql query

Question

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.

Gordon Linoff · Accepted Answer · 2017-06-03 22:49:29Z

1

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;

answered Jun 3, 2017 at 22:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Daryn Over a year ago

Thanks so much! By the way... Do you know how I would round the results to 2dp? The columns are numeric(20,8)

Daryn Over a year ago

Gordon Linoff Over a year ago

You would just add sum(value) as TOTAL for the last column in the SELECT.

Daryn Over a year ago

Is it possible to make it a sum of all the case statements? The reason being is I have rounded all the case statements to 2dp and now the totals are out if I do it that way.

Daryn Over a year ago

And just to make it worse... There are two days a year where the number of periods changes due to daylight savings. Is there a way to accommodate for this? e.g. one day will have 46 readings, and the other will have 50. On these days it must have the exact number of columns i.e. i cannot have 50 period columns for a date that only has 48 periods. Then there needs to be a column at the end with the sum of all period columns which all need to be rounded to 2dp.

Collectives™ on Stack Overflow

Postgres complicated sql query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related