CODEWITHSUNDEEP
sqlpostgresql
user1970850
user1970850

Reputation: 711

SQL query to find the max and min for each different day

I have the following table:

id     value truncated_day just_yyyy_mm_dd
1167    300 7/1/11 0:00  7/1/11
1167    301 7/1/11 0:00 7/1/11
1167    303 7/1/11 0:00 7/1/11
1167    308 7/1/11 0:00 7/1/11
1167    312 7/2/11 0:00 7/2/11
1167    316 7/2/11 0:00 7/2/11
1167    318 7/2/11 0:00 7/2/11
1167    330 7/2/11 0:00 7/2/11
1700    0   7/1/11 0:00 7/1/11
1700    10  7/1/11 0:00 7/1/11
1700    21  7/1/11 0:00 7/1/11
1700    33  7/1/11 0:00 7/1/11
1700    34  7/2/11 0:00 7/2/11
1700    35  7/2/11 0:00 7/2/11
1700    40  7/2/11 0:00 7/2/11

The table is actually very long (more than 32 million rows!!).

I would like to have the max and min values (read_value) for each day, and for each different id. I tried the following query but it does not work:

'$'select id, 
date_trunc('day', timestamp_utc) as truncated_day, 
substring(cast(date_trunc('day', timestamp_utc) as text) from 1 for 10) as just_yyyy_mm_dd,
max(value) as maxvalue,
min(value) as minvalue
from TABLE
order by device_id, truncated_day'$'

I basically would like the query to return:

id    min    max truncated_day just_yyyy_mm_dd
1167    300 308 7/1/11 0:00 7/1/11
1167    312 330 7/2/11 0:00 7/2/11
1700    0   33  7/1/11 0:00 7/1/11
1700    34  40  7/2/11 0:00 7/2/11

Could you please help me with this query? Thank you very much in advance! N

Upvotes: 4

Views: 2599

Answers (4)

alf
alf

Reputation: 18530

select id, min(value) as 'minvalue', max(value) as 'maxvalue', truncated_day, just_yy_mm_dd
from the_table
group by truncated_day, id, just_yy_mm_dd

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You need to change your order by to a group by. Actually, you probably want both:

select device_id, date_trunc('day', timestamp_utc) as truncated_day, 
           substring(cast(date_trunc('day', timestamp_utc) as text) from 1 for 10) as just_yyyy_mm_dd,
           max(value) as maxvalue,
            min(value) as minvalue
from TABLE
group by device_id, date_trunc('day', timestamp_utc)
order by device_id, truncated_day

I am curious whether group by device_id, truncated_day would work. Does the SQL compiler generate an error on the just_yyyy_mm_dd column? It doesn't need to generate an error, but it might not recognize that expression as being only on the aggregation columns.

Upvotes: 0

DWright
DWright

Reputation: 9500

Try this. Note addition of GROUP BY. You can add the just_yyyy_mm_dd stuff back in if you need it.

select id, 
date_trunc('day', timestamp_utc) as truncated_day, 
max(value) as maxvalue,
min(value) as minvalue
from TABLE
GROUP BY id, date_trunc('day', timestamp_utc)
order by device_id, truncated_day

Upvotes: 2

Gigi
Gigi

Reputation: 29421

You need a "group by", before the "order by". Basically you need to group by the first 3 fields.

Upvotes: 0

Related Questions