Postgres complicated SQL query

Question

I'm trying to create a query through Postgres. I have a table like this:

Date----------|Meter-----|------period-----|-----value
2017-05-23-----meter1  ---------- 1 ------------ 2.01
2017-05-23-----meter1  ---------- 3 ------------ 4.01
2017-05-23-----meter1  ---------- 4 ------------ 0.00

The table represents meter readings. There needs to be 48x meter readings per day. However sometimes for some reason a meter reading may not come through so there will be no record. Also, a meter reading could be 0.00.

What I need to do is generate a series that gives me something like this if I search for meter readings for any given meter on a particular date:

Period----Total
1     ---   2.01
2     ---   null
3     ---   4.01
4     ---   0.00
.
.
.

It needs to give me 48 readings even if there is no record. I'm trying to populate a google graph from a JSON response and the graph accepts null values but not 'no' values.

I appreciate any help.

Vamsi Prabhala · Accepted Answer · 2017-05-23 01:10:05Z

2

You can use generate_series to generate the numbers list and left join your table on to that and show missing periods as null.

select g.val,t.value
from generate_series(1,48) g(val)
left join tbl on t.period=g.val and t.date = '2017-05-23' --change this value as required

If you need it for all the dates and meters in the table, cross join generated periods with dates and meters and left join the table on to that.

select dm.date,dm.meter,g.val,t.value
from generate_series(1,48) g(val)
cross join (select distinct date,meter from t) dm
left join t on t.period=g.val and t.date=dm.date and t.meter=dm.meter
where dm.date = date '2017-05-23' --change this value as required
and dm.meter='meter1' --change this value as required

edited May 23, 2017 at 1:10

answered May 23, 2017 at 0:52

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

2 Comments

Leo C Over a year ago

Great solution. I think t.meter = 'meter1' is also needed as part of the left join condition in the first query.

Daryn Over a year ago

Thanks guys, helped a lot! :)

Collectives™ on Stack Overflow

Postgres complicated SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related