Sql-server – Aggregate interval data to hourly

countsql server

I am getting data for 15 minutes interval. I would like to aggregate this data to hourly.Can anybody advise how should i do this query to sum the interval values and display it?

Best Answer

You can do a simple GROUP BY with DATEPART applied to the time column. I'm assuming that you'll always get a row every 15 minutes and you don't need to worry about gaps in the data.

SELECT 
  code
, date_column AS [date]
, DATEPART(HOUR, time_column) AS hourly
, SUM(value) AS value
FROM test_table
GROUP BY
  code
, date_column
, DATEPART(HOUR, time_column);

The following references may be useful to you:

Related Solutions

Postgresql – Postgres Nested WHEN Aggregate Function

After some processing this boiled down to:

While your predicate d."SettlementPointName" = 'John' is filtering a single value for "SettlementPointName" anyway, simplify to:

SELECT count(                                     d."SettlementPointPrice" < 10.5 OR NULL) AS da_00_10
     , count(d."SettlementPointPrice" >= 10.5 AND d."SettlementPointPrice" < 20.5 OR NULL) AS da_11_20
     , count(d."SettlementPointPrice" >= 20.5 AND d."SettlementPointPrice" < 30.5 OR NULL) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."SettlementPointName" = 'John'
AND    d."DeliveryDate" >= '2015-02-01'
AND    d."DeliveryDate" <= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour";

About the counting technique:

For absolute performance, is SUM faster or COUNT?

Or better, yet, use the new aggregate filter technique in pg 9.4:

SELECT d."SettlementPointName"
     , count(*) FILTER (WHERE d."SettlementPointPrice" <  10.5) AS da_00_10
     , count(*) FILTER (WHERE d."SettlementPointPrice" >= 10.5
                        AND   d."SettlementPointPrice" <  20.5) AS da_11_20
     , count(*) FILTER (WHERE d."SettlementPointPrice" >= 20.5
                        AND   d."SettlementPointPrice" <  30.5) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."DeliveryDate" >= '2015-02-01'
AND    d."DeliveryDate" <= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour"
GROUP  BY 1;

This time, selecting all names and returning one row per name like you asked in the comment.

Details for FILTER:

Return counts for multiple ranges in a single SELECT statement

How to count the number of rows by datetime field with the division into time intervals

variable interval_minutes number;

exec :interval_minutes := 10;

select
  trunc(registered_at, 'HH24')
  + trunc(to_char(registered_at,'mi')/:interval_minutes)*:interval_minutes/1440, 
  count(*) 
from
  clients
group by 
  trunc(registered_at, 'HH24')
  + trunc(to_char(registered_at,'mi')/:interval_minutes)*:interval_minutes/1440
order by 
  1
;

Based on: Summarizing data over time - by time interval

Best Answer

Related Solutions

Postgresql – Postgres Nested WHEN Aggregate Function

How to count the number of rows by datetime field with the division into time intervals

Related Question