In article <1318661510830-4904685.p...@n5.nabble.com>,
"maya.more" <meena...@gmail.com> writes:

> I have a table with Date and unit column. . I want to find sum of unit column
> considering 3 days each

> User will specify start and enddate

> Eg 

> Date            Unit
> 10/1/2011     1
> 10/2/2011     2
> 10/3/2011      3
> 10/4/2011     4
> 10/5/2011     4
> 10/6/2011     1
> 10/7/2011     2 
> 10/8/2011     3
> 10/9/2011     1
> 10/10/2011    1
> 10/11/2011    1
> suppose if user selects date 10/1/2011  to 10/6/2011

> then output should be
 
> start date      enddate         unit
> 10/1/2011      10/3/2011     6
> 10/4/2011       10/6/2011    9

SELECT ('2011-10-01'::date + INTERVAL '3 day' * ((date - '2011-10-01') / 
3))::date AS start_date,
       ('2011-10-01'::date + INTERVAL '2 day' + INTERVAL '3 day' * ((date - 
'2011-10-01') / 3))::date AS end_date,
       sum(unit) AS unit
FROM tbl
WHERE date BETWEEN '2011-10-01' AND '2011-10-06'
GROUP BY 1, 2
ORDER BY 1


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply via email to