Thread: subquery question

subquery question

From
Sebastian Böhm
Date:
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

Re: subquery question

From
Bob Henkel
Date:
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
valueEND) 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@exse.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