postgresql - SQL - group data by day -

postgresql - SQL - group data by day -

i have table transaction info below:

date | status | delivered | products ------------------------------------------- 03.10.2014 | success | true | 4 03.10.2014 | failure | fail | 0 03.10.2014 | success | fail | 1 03.10.2014 | success | fail | 4 04.10.2014 | success | true | 24 04.10.2014 | success | true | 5 04.10.2014 | failure | fail | 0

now want grouping info day, it's like:

date | success_status | failure_status | delivered | total_products ------------------------------------------------------------------------- 03.10.2014 | 3 | 1 | 1 | 9 04.10.2014 | 2 | 1 | 2 | 29

i know how sum(products) total_products, group date.

but how can remaining columns success_status, failure_status , delivered in query?

thank you.

you need conditional sum:

select date, sum(case when status = 'success' , delivered products end) success_status, sum(case when status = 'failure' products end) failure_status, sum(case when delivered products end) delivered, sum(products) total_products the_table grouping date

in upcoming 9.4 version able write bit more elegantly (and might create clearer on case doing)

select date, sum(products) filter (status = 'success' , delivered) large_orders_amount, sum(products) filter (status = 'failure') failure_status, sum(products) filter (delivered) delivered, sum(products) total_products the_table grouping date

btw: date horrible name column. 1 because it's reserved word, more importantly doesn't document column contains. delivery date, receive date, cancel date, ...?

sql postgresql date group