I have this query
select * from sales
shop | date | hour | row_no | amount
-----------+------------+-----------+--------+-----------
shop_1 | 2012-08-14 | 00:08:00 | P01 | 10
shop_2 | 2012-08-12 | 00:12:00 | O05 | 40
shop_2 | 2012-08-12 | 00:12:00 | A01 | 20
I have 1 millon rows, I can do this query
select shop, SUM(amount)
from sales
group by shop
shop | amount |
-----------+------------+
shop_1 | 5666 |
shop_2 | 4044 |
shop_3 4044 |
But I need to spend the days at the columns and I do not know if they could help me do this
shop | 2012-08-1 | 2012-08-2 | 2012-08-3 |
-----------+------------+-----------+--------+-----------
shop_1 | 4005 | 5667 | 9987 |
shop_2 | 4333 | 4554 | 1234 |
shop_3 | 4555 | 6778 | 6677 |
Would be group by store in the rows, and group by days in the columns in postgresql
First, you must install tablefunc extension. Since version 9.1 you can do it using create extension:
CREATE EXTENSION tablefunc;
select * from crosstab (
select shop, date, SUM(amount)
from sales
group by shop
'select date from sales order by 1')
AS ct(shop: text, '2012-08-1' text, '2012-08-2' text, '2012-08-3' text)