sql-question: sum-function and subqueries?

[peter pilsl]

I have a question to sql and dont know exactely if this is the right
place to ask. If not I would be glad if anyone could point me to the
correct group/list/resource. Thnx.

I want to perform a sum-operation in a group by - query and want to
specify additionaly conditions when to sum.

like: having a table with colums x,y,z

select x,sum(y only if z is 'val1'),sum(y only if z is 'val2') from
table group by x;

real-world-example:

imagining the following table:

date | type | count
---------------------+-------+-------
2004-06-29 21:29:26 | index | 1
2004-06-29 21:29:26 | pic | 35
2004-06-29 21:29:26 | raw | 1
2004-06-30 09:47:18 | index | 5
2004-06-30 09:47:18 | index | 1
2004-06-30 09:47:18 | index | 1
2004-06-30 09:47:18 | index | 2
2004-06-30 09:47:18 | pic | 51
2004-06-30 09:47:18 | index | 2
2004-06-30 09:47:18 | index | 3

I want to perform a select and group by date and sum up count.

#select date,sum(count) from log group by date;
date | sum
---------------------+-----
2004-06-29 21:29:26 | 37
2004-06-30 09:47:18 | 65

so good so far. Now I'd like to group by date again, but got more
sum-columns depending on the type. So I want to have the sum over all
counts where type is raw, where type is index and where type is pic and
so on.
Is there away to do so in a single query? (and would it save resources
compared to four queries with simple where-conditions ?)

date | sum_total | sum_raw | sum_pic | sum_index
---------------------+--------------------------------------------
2004-06-29 21:29:26 | 37 | 1 | 35 | 1
2004-06-30 09:47:18 | 65 | 0 | 51 | 14

thnx a lot,
peter


--
http://www2.goldfisch.at/know_list
http://leblogsportif.sportnation.at

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Bruno Wolff III]

On Thu, Jul 01, 2004 at 10:56:29 +0200,
peter pilsl <pi***@goldfisch.at> wrote:

I have a question to sql and dont know exactely if this is the right
place to ask. If not I would be glad if anyone could point me to the
correct group/list/resource. Thnx.

I want to perform a sum-operation in a group by - query and want to
specify additionaly conditions when to sum.

like: having a table with colums x,y,z

select x,sum(y only if z is 'val1'),sum(y only if z is 'val2') from
table group by x;

You can use CASE to do this. The CASE function goes inside the sum
function call similar to what you sketched out above.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html