pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGroup by on Date
3+ messages / 3 participants
[nested] [flat]
* Group by on Date
@ 2011-10-15 06:51 maya.more <meena.mk@gmail.com>
2011-10-16 08:27 ` Re: Group by on Date Andreas Kretschmer <akretschmer@spamfence.net>
2011-10-16 08:35 ` Re: Group by on Date Harald Fuchs <hari.fuchs@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: maya.more @ 2011-10-15 06:51 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Group by on Date
2011-10-15 06:51 Group by on Date maya.more <meena.mk@gmail.com>
@ 2011-10-16 08:27 ` Andreas Kretschmer <akretschmer@spamfence.net>
1 sibling, 0 replies; 3+ messages in thread
From: Andreas Kretschmer @ 2011-10-16 08:27 UTC (permalink / raw)
To: pgsql-sql
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 | 1
2011-10-18 | 2
2011-10-19 | 3
2011-10-20 | 4
2011-10-21 | 5
2011-10-22 | 6
2011-10-23 | 7
2011-10-24 | 8
2011-10-25 | 9
2011-10-26 | 10
2011-10-27 | 11
2011-10-28 | 12
2011-10-29 | 13
2011-10-30 | 14
2011-10-31 | 15
2011-11-01 | 16
2011-11-02 | 17
2011-11-03 | 18
2011-11-04 | 19
2011-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 | 6
2011-10-19 00:00:00 | 2011-10-22 00:00:00 | 15
2011-10-22 00:00:00 | 2011-10-25 00:00:00 | 24
2011-10-25 00:00:00 | 2011-10-28 00:00:00 | 33
2011-10-28 00:00:00 | 2011-10-31 00:00:00 | 42
2011-10-31 00:00:00 | 2011-11-03 00:00:00 | 51
2011-11-03 00:00:00 | 2011-11-06 00: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°
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Group by on Date
2011-10-15 06:51 Group by on Date maya.more <meena.mk@gmail.com>
@ 2011-10-16 08:35 ` Harald Fuchs <hari.fuchs@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Harald Fuchs @ 2011-10-16 08:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-10-16 08:35 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-15 06:51 Group by on Date maya.more <meena.mk@gmail.com>
2011-10-16 08:27 ` Andreas Kretschmer <akretschmer@spamfence.net>
2011-10-16 08:35 ` Harald Fuchs <hari.fuchs@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox