Old challenges, new synthax ...

I've blogged before about the new SQL synthax which is becoming available in databases and how it helps solving questions which are increasingly common.

Now it's time for another example, something which doesn't come up often in a reporting environment as most tools have this feature, but can be a problem if you're building your output with a scipting language.

Adding a "Total" row at the end of your tabular output, that's it! Here you can find the Devshed forum post that prompted this summary.

Aside from the classical solution using a UNION and an ORDER BY query, I suggested using the new WITH ROLLUP clause, which is implemented by many commercial databases and MySQL (available from 4.1).

Unfortunately MySQL's implementations poses some limitations to the ORDER BY clause, actually it's impossible to directly order by the aggregate column, which is a quite common requirement, on with the main problem using the Sakila sample database.

An example requirement could be showing all sales by category and a row holding the grand total.

In Sakila the basic query would be:

SELECT
 c.name AS category,
 SUM(p.amount) AS total_sales
FROM
 sakila.payment p
  JOIN sakila.rental r
   ON p.rental_id = r.rental_id
  JOIN sakila.inventory i
   ON r.inventory_id = i.inventory_id
  JOIN sakila.film f
   ON i.film_id = f.film_id
  JOIN sakila.film_category fc
   ON f.film_id = fc.film_id
  JOIN sakila.category c
   ON fc.category_id = c.category_id
GROUP BY
 c.name; 

As I said a requirement based on ordering by total_sales in MySQL can't be satisfied without using a trick, like nesting selects, and so I'll skip it (BTW: this made me notice a bad practice left around in Sakila, usage of order by in views, see "sales_by_film_category") .

The traditional solution for adding a total row would be:

SELECT
 c.name AS category,
 SUM(p.amount) AS total_sales
FROM
 sakila.payment p
  JOIN sakila.rental r
   ON p.rental_id = r.rental_id
  JOIN sakila.inventory i
   ON r.inventory_id = i.inventory_id
  JOIN sakila.film f
   ON i.film_id = f.film_id
  JOIN sakila.film_category fc
   ON f.film_id = fc.film_id
  JOIN sakila.category c
   ON fc.category_id = c.category_id
GROUP BY
 c.name
UNION ALL
SELECT
 NULL,
 SUM(p.amount) AS total_sales
FROM
 sakila.payment p
  JOIN sakila.rental r
   ON p.rental_id = r.rental_id
  JOIN sakila.inventory i
   ON r.inventory_id = i.inventory_id
  JOIN sakila.film f
   ON i.film_id = f.film_id
  JOIN sakila.film_category fc
   ON f.film_id = fc.film_id
  JOIN sakila.category c
   ON fc.category_id = c.category_id
ORDER
    BY CASE WHEN category IS NULL
            THEN 'last'
            ELSE 'first' END
     , category 

As you can see it can be quite hard to read ... the order by trick is last.

The explain plan can become quite nasty too ... (MySQLPerformanceBlog wrote about this earlier), now have a look at the more modern solution:

SELECT
 c.name AS category,
 SUM(p.amount) AS total_sales
FROM
 sakila.payment p
  JOIN sakila.rental r
   ON p.rental_id = r.rental_id
  JOIN sakila.inventory i
   ON r.inventory_id = i.inventory_id
  JOIN sakila.film f
   ON i.film_id = f.film_id
  JOIN sakila.film_category fc
   ON f.film_id = fc.film_id
  JOIN sakila.category c
   ON fc.category_id = c.category_id
GROUP BY
 c.name
WITH rollup; 

Much simpler, isn't it?

But what about efficiency?
Let's look at explain plans for those queries:

First one, the classic way:

mysql> EXPLAIN EXTENDED select
->  c.name AS category,
->  sum(p.amount) AS total_sales
-> from
->  sakila.payment p
->   join sakila.rental r
->    on p.rental_id = r.rental_id
->   join sakila.inventory i
->    on r.inventory_id = i.inventory_id
->   join sakila.film f
->    on i.film_id = f.film_id
->   join sakila.film_category fc
->    on f.film_id = fc.film_id
->   join sakila.category c
->    on fc.category_id = c.category_id
-> group by
->  c.name
-> union all
-> select
->  null,
->  sum(p.amount) AS total_sales
-> from
->  sakila.payment p
->   join sakila.rental r
->    on p.rental_id = r.rental_id
->   join sakila.inventory i
->    on r.inventory_id = i.inventory_id
->   join sakila.film f
->    on i.film_id = f.film_id
->   join sakila.film_category fc
->    on f.film_id = fc.film_id
->   join sakila.category c
->    on fc.category_id = c.category_id
-> ORDER
->     BY CASE WHEN category IS NULL
->             THEN 'last'
->             ELSE 'first' END
->      , category;
+----+--------------+------------+--------+-----------------------------------+---------------------------+---------+-----------------------+------+----------+---------------------------------+
| id | select_type  | table      | type   | possible_keys                     |key                       | key_len | ref                   | rows | filtered |Extra                           |
+----+--------------+------------+--------+-----------------------------------+---------------------------+---------+-----------------------+------+----------+---------------------------------+
|  1 | PRIMARY      | c          | ALL    | PRIMARY                           |NULL                      | NULL    | NULL                  |   16 |   100.00 |Using temporary; Using filesort |
|  1 | PRIMARY      | fc         | ref    | PRIMARY,fk_film_category_category |fk_film_category_category | 1       | sakila.c.category_id  |    9 |   100.00 |Using index                     |
|  1 | PRIMARY      | f          | eq_ref | PRIMARY                           |PRIMARY                   | 2       | sakila.fc.film_id     |    1 |   100.00 |Using index                     |
|  1 | PRIMARY      | i          | ref    | PRIMARY,idx_fk_film_id            |idx_fk_film_id            | 2       | sakila.fc.film_id     |    2 |   100.00 |Using index                     |
|  1 | PRIMARY      | r          | ref    | PRIMARY,idx_fk_inventory_id       |idx_fk_inventory_id       | 3       | sakila.i.inventory_id |    1 |   100.00 |Using index                     |
|  1 | PRIMARY      | p          | ref    | fk_payment_rental                 |fk_payment_rental         | 5       | sakila.r.rental_id    |    1 |   100.00 |Using where                     |
|  2 | UNION        | c          | index  | PRIMARY                           |PRIMARY                   | 1       | NULL                  |   16 |   100.00 |Using index                     |
|  2 | UNION        | fc         | ref    | PRIMARY,fk_film_category_category |fk_film_category_category | 1       | sakila.c.category_id  |    9 |   100.00 |Using index                     |
|  2 | UNION        | f          | eq_ref | PRIMARY                           |PRIMARY                   | 2       | sakila.fc.film_id     |    1 |   100.00 |Using index                     |
|  2 | UNION        | i          | ref    | PRIMARY,idx_fk_film_id            |idx_fk_film_id            | 2       | sakila.fc.film_id     |    2 |   100.00 |Using index                     |
|  2 | UNION        | r          | ref    | PRIMARY,idx_fk_inventory_id       |idx_fk_inventory_id       | 3       | sakila.i.inventory_id |    1 |   100.00 |Using index                     |
|  2 | UNION        | p          | ref    | fk_payment_rental                 |fk_payment_rental         | 5       | sakila.r.rental_id    |    1 |   100.00 |Using where                     |
| NULL | UNION RESULT |  | ALL    | NULL| NULL                      | NULL    | NULL                  | NULL |     NULL| Using filesort                  |
+----+--------------+------------+--------+-----------------------------------+---------------------------+---------+-----------------------+------+----------+---------------------------------+
13 rows in set, 1 warning (0.00 sec)

mysql>

Pretty bad isn't it? Two queries are executed, then glued together.

Now the second one WITH ROLLUP:

mysql> EXPLAIN EXTENDED select
->  c.name AS category,
->  sum(p.amount) AS total_sales
-> from
->  sakila.payment p
->   join sakila.rental r
->    on p.rental_id = r.rental_id
->   join sakila.inventory i
->    on r.inventory_id = i.inventory_id
->   join sakila.film f
->    on i.film_id = f.film_id
->   join sakila.film_category fc
->    on f.film_id = fc.film_id
->   join sakila.category c
->    on fc.category_id = c.category_id
-> group by
->  c.name
-> with rollup;
+----+-------------+-------+--------+-----------------------------------+---------------------------+---------+-----------------------+------+----------+----------------+
| id | select_type | table | type   | possible_keys                     | key                    | key_len | ref                   | rows | filtered | Extra         |
+----+-------------+-------+--------+-----------------------------------+---------------------------+---------+-----------------------+------+----------+----------------+
|  1 | SIMPLE      | c     | ALL    | PRIMARY                           | NULL                    | NULL    | NULL                  |   16 |   100.00 | Using filesort |
|  1 | SIMPLE      | fc    | ref    | PRIMARY,fk_film_category_category | fk_film_category_category | 1       | sakila.c.category_id  |    9 |   100.00 | Using index    |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY                           | PRIMARY                   | 2       | sakila.fc.film_id     |    1 |   100.00 | Using index    |
|  1 | SIMPLE      | i     | ref    | PRIMARY,idx_fk_film_id            | idx_fk_film_id            | 2       | sakila.fc.film_id     |    2 |   100.00 | Using index    |
|  1 | SIMPLE      | r     | ref    | PRIMARY,idx_fk_inventory_id       | idx_fk_inventory_id       | 3       | sakila.i.inventory_id |    1 |   100.00 | Using index    |
|  1 | SIMPLE      | p     | ref    | fk_payment_rental                 | fk_payment_rental         | 5       | sakila.r.rental_id    |    1 |   100.00 | Using where    |
+----+-------------+-------+--------+-----------------------------------+---------------------------+---------+-----------------------+------+----------+----------------+
6 rows in set, 1 warning (0.00 sec)

mysql>

A lot better, isn't it? And faster ;)

BTW: More about EPLAIN EXTENDED here.

Random selection, with a bias ...

Say you want to randomly select your employee of the month, but not so randomly, better, you'd like to give your best employees a bigger chance to be selected based on their rating.
This is just an example, you could be randomly displaying ads from your customers, but giving an higher chance to be displayed to those who are paying more, there can be a million other example, but I hope you got the sense of this.
In other terms this means to add some skew to your data, so that it's distribution is no more uniform.
Apart from the statistical implications of this, let's see the sql in action, I'll be using Firebird's Employee sample database, first of all we'll add a column to the employee table, holding the employee rating, then set the rating to 5 for employee 2, 10 for employee 4, 15 for employee 5, 1 for all other employees.
Now that we have rated each employee we need the magic to have them randomly selected with a bias ...
This is accomplished with a "sequence table" which will do just one thing, hold a list of numbers starting from the minimum rating value to the max rating (you'll probably want to preload it with a lot of numbers starting from 1).
Here is the sql:

CREATE TABLE CONSECUTIVE_NUMBER(
NUM Integer NOT NULL,
CONSTRAINT PK_CONSECUTIVE_NUMBER_1 PRIMARY KEY (NUM)
);

And load it:

insert Into Consecutive_number (num) Values (1);
Insert Into Consecutive_number (num) Values (2);
Insert Into Consecutive_number (num) Values (3);
Insert Into Consecutive_number (num) Values (4);
Insert Into Consecutive_number (num) Values (5);
Insert Into Consecutive_number (num) Values (6);
Insert Into Consecutive_number (num) Values (7);
Insert Into Consecutive_number (num) Values (8);
Insert Into Consecutive_number (num) Values (9);
Insert Into Consecutive_number (num) Values (10);
Insert Into Consecutive_number (num) Values (11);
Insert Into Consecutive_number (num) Values (12);
Insert Into Consecutive_number (num) Values (13);
Insert Into Consecutive_number (num) Values (14);
Insert Into Consecutive_number (num) Values (15);
Insert Into Consecutive_number (num) Values (16);
Insert Into Consecutive_number (num) Values (17);
Insert Into Consecutive_number (num) Values (18);
Insert Into Consecutive_number (num) Values (19);
Insert Into Consecutive_number (num) Values (20);

Now we are ready for the magic, we'll be joining our employee table with this sequence table, generating a specific cross join which will hold as many rows for each employee as it's rating, thus, when doing a random select on this data there will be a bigger chance to choose some employees, i.e. employee number 5 will have 15 times the chance to be selected than employee number 8 and 3 times the chances of employee number 2.
Here is the query, embedded in a view:

CREATE VIEW
RANDOM_SKEWED_EMPLOYEE (EMP_NO)
AS
/* write select statement here */
SELECT
FIRST 1
a.EMP_NO
FROM
EMPLOYEE a
INNER JOIN
CONSECUTIVE_NUMBER c
ON
c.NUM BETWEEN 1 AND a.RATING
ORDER BY
rand();

The join clause does the magic, as I said.
Now extract a small sample:

24
5
36
85
5
2
12
24
144
5
5
20
94
5
4
127
5
94
85
4
2
34
136
4
9

Let's see it's distribution, even on such a small sample you'll see that those 3 employees appear more often than others and with more or less the expected relative ratio:

Emp_no Times
2 5
4 9
5 15
9 4
14 1
20 1
28 2
29 2
36 1
37 1
52 2
71 1
72 1
83 1
113 1
114 1
118 2
121 1
127 1
138 2
144 1

Of course a larger sample will be closer to the desired result.
This example is buit on Firebird, but it should work in any database.
HTH

Note that PostgreSQL's generate_series() instruction might help a lot ;)