CODEWITHSUNDEEP
sqlpostgresqltime-series
user3740429
user3740429

Reputation: 41

How to Create a Daily Sum of Records from a table with start and end fields in SQL

I have the following table 

startdate   enddate activity
1/1/2015    1/3/2015    foo
1/2/2015    1/6/2015    bar
1/4/2015    1/7/2015    bar

How do i query to get the following daily sum?

date    foo bar
1/1/2015    1   0
1/2/2015    1   1
1/3/2015    0   1
1/4/2015    0   2
1/5/2015    0   2
1/6/2015    0   1
1/7/2015    0   0

i'm using PSQL if that helps.

I'm assuming this is an easy question but i can't seem to find answer when i search. Perhaps i'm not using the right terminology to describe. thanks for any help

Upvotes: 2

Views: 104

Answers (2)

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

We can make it more dynamic by generating more activity cases.

With reference from Gordon Linoff's answer , I have created postgresql function which can make it more dynamic for activity cases.

Check following function:

create or replace function  sp_test()
returns void as
$$

declare cases character varying;
declare sql_statement text;
begin



select string_agg(distinct concat('sum((activity=','''',activity,'''',' )::int) as ',activity,' '),',') into cases from your_tbl;

drop table if exists temp_data;

sql_statement=concat('create temporary table temp_data as select t.dte, ',cases,'from 
(select generate_series(startdate, enddate, interval ','''1 day''',') as dte,
         activity
      from your_tbl) as t group by t.dte order by t.dte');

execute sql_statement;


end;
$$
language 'plpgsql';

Function uses temporary table to store dynamic columns data.

Call function in following way to get data:

select * from sp_test(); select * from temp_data;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can use generate_series() and aggregation. So, something like:

select dte,
       sum((activity = 'foo')::int) as foo,
       sum((activity = 'bar')::int) as bar
from (select generate_series(startdate, enddate, interval '1 day') as dte,
             activity
      from t
     ) t
group by dte
order by dte;

Note that generate_series() is highly Postgres-specific. It is, however, highly convenient for this purpose.

Upvotes: 2

Related Questions