ruby on rails - mysql pivot to a postgres pivot table -

i using mysql fine until switched 1 of rails apps heroku , had change over. works expected except have 1 query totally funky.

this postgres, under mysql identical except extract dow , group additions, isn't problem, problem used sum days of week listed, sums entire table... , avg off since gets table avg , not days listed.

is there way sum of listed days without having select, i'm missing?... avoid doing select ( select ... ) subquery sum of columns.

thanks

select rooms.name, rooms.id, max(case when (extract(dow availables.bookdate) - extract(dow date '2010-01-20')) = -3 (availables.price*1) else 0 end) day1, max(case when (extract(dow availables.bookdate) - extract(dow date '2010-01-20')) = -2 (availables.price*1) else 0 end) day2, max(case when (extract(dow availables.bookdate) - extract(dow date '2010-01-20')) = -1 (availables.price*1) else 0 end) day3, (avg(availables.price)*1) avg, (sum(availables.price)*1) * 2 sum,  max((sign(spots)-1) + 2) beds  availables  inner join rooms  on availables.room_id=rooms.id  availables.room_id = '1818' , availables.price > 0  group rooms.id, rooms.name 

you didn't schema is, pretended of data in 1 table, omitting join. should no trouble replace "stuff" join.

i created simple table stand in join:

wayne=# \d stuff                             table "pg_temp_1.stuff"   column  |     type      |                     modifiers ----------+---------------+----------------------------------------------------  id       | integer       | not null default nextval('stuff_id_seq'::regclass)  room_id  | integer       | not null  bookdate | date          | not null  price    | numeric(10,2) | not null indexes:     "stuff_pkey" primary key, btree (id) 

added data it:

wayne=# select * stuff;  id | room_id |  bookdate  | price ----+---------+------------+-------   1 |       1 | 2010-01-11 | 60.00   2 |       1 | 2010-01-10 | 60.00   3 |       2 | 2010-01-10 | 55.00   4 |       2 | 2010-01-09 | 55.00   5 |       3 | 2010-01-09 | 70.00   6 |       3 | 2010-01-08 | 70.00 (6 rows) 

and here's query last 2 full days, plus today, grouped date, count, sum , avg price.

wayne=# select bookdate, count(*), sum(price), avg(price) stuff \ bookdate >= date_trunc('day', now()) - interval '2 days' \ group bookdate order bookdate;   bookdate  | count |  sum   |         avg ------------+-------+--------+---------------------  2010-01-09 |     2 | 125.00 | 62.5000000000000000  2010-01-10 |     2 | 115.00 | 57.5000000000000000  2010-01-11 |     1 |  60.00 | 60.0000000000000000 (3 rows)