From: Harald Fuchs <hari.fuchs@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Group by on Date
Date: Sun, 16 Oct 2011 10:35:13 +0200
Message-ID: <86k485tjum.fsf@protecting.net> (raw)
References: <1318661510830-4904685.post@n5.nabble.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hari.fuchs@gmail.com
Subject: Re: Group by on Date
In-Reply-To: <86k485tjum.fsf@protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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