mysql pivot to a postgres pivot table...

Question

I was using mysql just fine until I recently switched one of my rails apps to heroku and had to change over. Almost everything works as expected except I have one query which does something totally funky.

This is the postgres, but under mysql it is mostly identical except for the EXTRACT DOW and some group by additions, but that isn't the problem, the problem is it used to SUM the days of the week listed, now it sums the entire table... and also the AVG is off since it also gets the table avg and not the days listed.

Is there a way to get a sum of the listed days without having to do another select, something i'm missing?... I would like to avoid doing SELECT ( SELECT ... ) as SUBQUERY just to get a sum of the columns.

Thanks

SELECT rooms.name, rooms.id,
MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -3 THEN (availables.price*1) ELSE 0 END) AS day1,
MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -2 THEN (availables.price*1) ELSE 0 END) AS day2,
MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -1 THEN (availables.price*1) ELSE 0 END) AS day3,
(AVG(availables.price)*1) AS avg,
(SUM(availables.price)*1) * 2 AS sum, 
MAX((SIGN(spots)-1) + 2) AS beds
 FROM availables
 INNER JOIN rooms
 ON availables.room_id=rooms.id
 WHERE availables.room_id = '1818' AND availables.price > 0
 GROUP BY rooms.id, rooms.name

Answer 1

You didn't say what the schema is, so I pretended all of the data was in one table, omitting the join. It should be no trouble to replace "stuff" with your join.

I created a simple table to stand in for your 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 some data to it:

wayne=# select * from 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 a query for the last two full days, plus today, grouped by date, with count, sum and avg price.

wayne=# select bookdate, count(*), sum(price), avg(price) from stuff \
where bookdate >= date_trunc('day', now()) - interval '2 days' \
group by bookdate order by 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)

Answer 2

Sorry, I should have included what the output looks like:

+------+--------+------------+-------+-------+-------+------+---------------------+
| id   | sum    | name       | day1  | day2  | day3  | beds | avg                 |
+------+--------+------------+-------+-------+-------+------+---------------------+
| 1819 | 131.52 | 8 Bed Dorm | 21.92 | 21.92 | 21.92 | 2    | 21.8980952380952381 |
+------+--------+------------+-------+-------+-------+------+---------------------+

And the input:

+----+-------+-------+------------+---------+---------------------------+---------------------------+
| id | price | spots | bookdate   | room_id | created_at                | updated_at                |
+----+-------+-------+------------+---------+---------------------------+---------------------------+
| 1  | 27.72 | 1     | 2009-09-14 | 1       | 2009-09-11 15:32:22 +0200 | 2009-09-11 15:32:22 +0200 |
+----+-------+-------+------------+---------+---------------------------+---------------------------+

Below works and does what I want it to, except for the avgs but it's really messy... as I couldn't figure out how to sum in any other way without getting sums of all nights instead of just 2-5... ie $120 vs $20,512. The solution below was to do the same MAX as above for pivoted days... day1 day2... and just doing the same to add them together.

The join is not important, its only to pull the name of the room.

SELECT rooms.name, rooms.id,
MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -3 THEN (availables.price*1) ELSE 0 END) AS day1,
MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -2 THEN (availables.price*1) ELSE 0 END) AS day2,
MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -1 THEN (availables.price*1) ELSE 0 END) AS day3,
(MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -3 THEN (availables.price*1) ELSE 0 END) + 
MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -2 THEN (availables.price*1) ELSE 0 END) + 
MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -1 THEN (availables.price*1) ELSE 0 END)) *1 * 2 AS sum, 
(AVG(availables.price)*1) AS avg, 
MAX((SIGN(spots)-1) + 2) AS beds
 FROM availables
 INNER JOIN rooms
 ON availables.room_id=rooms.id
 WHERE availables.room_id = '1819' AND availables.price > 0
 GROUP BY rooms.id, rooms.name

Answer 3

All you need to do is limit the results to the past 3 days. This will prevent the avg/sum from being performed on the entire table...Add this to your existing query (taken from Wayne, who got a +1 for the effort)

AND availables.bookdate >= date_trunc('day', now()) - interval '2 days'