Hack 31. Disaggregate a COUNT

COUNT will consolidate a group of rows and tell you how many rows were in each group. But what if you wanted to reverse that process, and split the consolidated result back into multiple rows?

Once you've consolidated some data, it can be tricky to take it apart. For example, suppose you have a table containing hotel room bookings, as shown in Table 5-9. Each row has a date to indicate the first night of the booking, the total cost, and the total number of nights booked.

Table 5-9. The bookings table

startWhn	visitPrice	nights
2005-01-01	100	2
2005-02-01	200	5

Figuring out how many rooms are booked on a particular night is difficult given this information. You want to process this information so that the result has one row for each night the guest is staying. A result like this is useful if you want to find out how many rooms are free on a particular night. The price shown is the cost per day. Table 5-10 shows the desired result.

Table 5-10. The desired table (desired booking information)

startWhn	whn	price
2005-01-01	2005-01-01	50
2005-01-01	2005-01-02	50
2005-02-01	2005-02-01	40
2005-02-01	2005-02-02	40
2005-02-01	2005-02-03	40
2005-02-01	2005-02-04	40
2005-02-01	2005-02-05	40

It is easy to go from desired to booking, but that is the opposite of what you need:

SELECT StartWhn,sum(Price),count(Price)
 FROM desired
 GROUP BY StartWhn

To go from booking to desired requires more thought. Joe Celko, author of SQL Puzzles and Answers (Morgan Kaufmann), calls this the "uncount" problem. You can solve this using an integers table [Hack #82].

An integers table contains a single column, with multiple rows holding the numbers from 1 to some high number. In the case of this hack, the integers table must at least go up to the maximum number of nights that any customer may book. So:

CREATE TABLE integers (n INT PRIMARY KEY);
INSERT INTO integers (n) VALUES (1);
INSERT INTO integers (n) VALUES (2);
INSERT INTO integers (n) VALUES (3);
INSERT INTO integers (n) VALUES (4);
INSERT INTO integers (n) VALUES (5);
...

integers is a useful table in a variety of queries, and you can generate it with a few thousand rows quite quickly using a script [Hack #82].

You don't have to create the integers table; instead, you can use an inline table [Hack #63].

The key to producing desired is to join integers to booking as a cross-join, with a rule that n is never bigger than nights:

mysql> SELECT startWhn,
 -> CAST(startWhn + n - 1 AS DATE) AS whn,
 -> visitPrice/nights AS price
 -> FROM booking,integers
 -> WHERE n BETWEEN 1 AND nights;
+------------+------------+---------+
| startWhn | whn | price |
+------------+------------+---------+
| 2005-01-01 | 2005-01-01 | 50.0000 |
| 2005-01-01 | 2005-01-02 | 50.0000 |
| 2005-02-01 | 2005-02-01 | 40.0000 |
| 2005-02-01 | 2005-02-02 | 40.0000 |
| 2005-02-01 | 2005-02-03 | 40.0000 |
| 2005-02-01 | 2005-02-04 | 40.0000 |
| 2005-02-01 | 2005-02-05 | 40.0000 |
+------------+------------+---------+

This form works on all the main platforms, but in SQL Server you should CAST as DATETIME rather than DATE. In Access, you can just ignore the CAST and use startWhn+n-1 AS whn.