Thread: Group by on Date

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


pls let me know if anybody has any idea.

Thanks in advance 


--
View this message in context: http://postgresql.1045698.n5.nabble.com/Group-by-on-Date-tp4904685p4904685.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

maya.more <meena.mk@gmail.com> wrote:

> 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
> 
> 
> pls let me know if anybody has any idea.

Maybe something like that:

test=*# select * from d;    d      | i
------------+----2011-10-17 |  12011-10-18 |  22011-10-19 |  32011-10-20 |  42011-10-21 |  52011-10-22 |  62011-10-23 |
72011-10-24 |  82011-10-25 |  92011-10-26 | 102011-10-27 | 112011-10-28 | 122011-10-29 | 132011-10-30 | 142011-10-31 |
152011-11-01| 162011-11-02 | 172011-11-03 | 182011-11-04 | 192011-11-05 | 20
 
(20 rows)

Time: 0,276 ms
test=*# select '2011-01-01'::date + (extract(doy from d) / 3)::int *
'3day'::interval - '3day'::interval as "from", '2011-01-01'::date +
(extract(doy from d) / 3)::int * '3day'::interval as "to", sum(i) from d
group by 1,2 order by 1;       from         |         to          | sum
---------------------+---------------------+-----2011-10-16 00:00:00 | 2011-10-19 00:00:00 |   62011-10-19 00:00:00 |
2011-10-2200:00:00 |  152011-10-22 00:00:00 | 2011-10-25 00:00:00 |  242011-10-25 00:00:00 | 2011-10-28 00:00:00 |
332011-10-2800:00:00 | 2011-10-31 00:00:00 |  422011-10-31 00:00:00 | 2011-11-03 00:00:00 |  512011-11-03 00:00:00 |
2011-11-0600:00:00 |  39
 
(7 rows)

it fails for more than 1 year, but i hope, you can modify my sql for
your own.



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

In article <1318661510830-4904685.post@n5.nabble.com>,
"maya.more" <meena.mk@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