From: | Bob Henkel <bob(dot)henkel(at)gmail(dot)com> |
---|---|
To: | Sebastian Böhm <seb(at)exse(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: subquery question |
Date: | 2009-03-13 04:31:06 |
Message-ID: | fedea56b0903122131u494b7b2aj89fdc0cd7283b905@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Does this help
Here is my test table data.
ID;DATE;VALUE
1;"2009-03-13";5
2;"2009-03-13";2
3;"2009-03-11";1
4;"2009-03-11";2
5;"2009-03-11";3
SELECT mydate AS day,
SUM(CASE WHEN id % 2 = 1 THEN value END) AS sum_odd,
SUM(CASE WHEN id % 2 = 0 THEN value END) AS sum_even
FROM xyz
GROUP BY mydate;
DATE;SUM_ODD;SUM_EVEN
"2009-03-11";4;2
"2009-03-13";5;2
Check the plans generated to see if one query actually appears better
than another.
Bob
On Thu, Mar 12, 2009 at 9:06 PM, Sebastian Böhm <seb(at)exse(dot)net> wrote:
> Hi,
> I have a table: (date timestamp, id integer, value integer)
> What Iam trying to do is to get a result that looks like this:
> day sum_odd sum_even
> 2009-01-01 6565 78867
> 2009-01-02 876785 87667
>
> basically a need to combine these two queries into one:
> SELECT date_trunc('day',date) AS day, sum(value) AS sum_odd FROM
> xyz WHERE id % 2 = 1 GROUP BY date_trunc('day',date)
> SELECT date_trunc('day',date) AS day, sum(value) AS sum_even FROM
> xyz WHERE id % 2 = 0 GROUP BY date_trunc('day',date)
> I found various ways to do this via unions or joins, but none of them seem
> efficient, what is the best way to do that ?
>
> thank you very much
> Sebastian
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Dam | 2009-03-13 08:38:08 | Re: Convert text from UTF8 to ASCII |
Previous Message | Sebastian Böhm | 2009-03-13 03:06:45 | subquery question |