Hack 33. Get Values and Subtotals in One Shot

If you need to show values with totals and subtotals in a report, you could make two separate queries. But it is safer and quicker to do both in one go.

You might need to produce a report that shows values and calculates subtotals. In Table 5-13, the subtotals are listed at the end of each group.

Table 5-13. Table values and subtotals

item	serialNumber	price
Awl	1	10
Awl	3	10
Awl	subtotal	20
Bowl	2	10
Bowl	5	10
Bowl	6	10
Bowl	subtotal	30
Cowl	4	10
Cowl	subtotal	10

For a simple query like this, the time cost of the round trip to the database server may dominate the cost of the query itself. So, making two trips to the database is a lot like going to the bar to buy a drink, walking away, and then going back to the bar for your change.

More than one trip to the database also introduces the possibility that someone else has added a row between your visits. If that happens, your total could be wrong.

A simple UNION will add the subtotals to your result:

mysql> SELECT item, serialNumber, price FROM source
 -> UNION
 -> SELECT item, NULL, SUM(price)
 -> FROM source
 -> GROUP BY item
 -> ORDER BY item;
+------+--------------+-------+
| item | serialNumber | price |
+------+--------------+-------+
| Awl | NULL | 20 |
| Awl | 1 | 10 |
| Awl | 3 | 10 |
| Bowl | NULL | 30 |
| Bowl | 2 | 10 |
| Bowl | 5 | 10 |
| Bowl | 6 | 10 |
| Cowl | NULL | 10 |
| Cowl | 4 | 10 |
+------+--------------+-------+

The database server still has roughly the same amount of work to do as before, but the overall performance should be improved because you have saved the cost of sending a query and getting the results back.

The query works fine. The only slight problem is that subtotals show up as the first row for each item (depending on the SQL implementation you are using). Traditionally, the subtotals show up as the last item in each run.

You can change the ORDER BY expression to c, COALESCE(seq, 1E9). This will ensure that the NULL values in the serialNumber column show up last (1E9 is the number 1,000,000,000). You can have the UNION as a derived table to make it clear that the ORDER BY applies to the whole UNION:

mysql> SELECT item, serialNumber, price FROM(
 -> SELECT item, serialNumber, price FROM source
 -> UNION
 -> SELECT item, NULL, SUM(price)
 -> FROM source
 -> GROUP BY item
 -> ) t
 -> ORDER BY item, COALESCE(serialNumber,1E9);
+------+--------------+-------+
| item | serialNumber | price |
+------+--------------+-------+
| Awl | 1 | 10 |
| Awl | 3 | 10 |
| Awl | NULL | 20 |
| Bowl | 2 | 10 |
| Bowl | 5 | 10 |
| Bowl | 6 | 10 |
| Bowl | NULL | 30 |
| Cowl | 4 | 10 |
| Cowl | NULL | 10 |
+------+--------------+-------+

 

5.10.1. ROLLUP and GROUPING SETS

SQL Server, MySQL, and DB2 can do this using the WITH ROLLUP clause. Oracle has a GROUPING SETS clause that covers this (DB2 supports this as well).

5.10.1.1. SQL Server, MySQL, and DB2

In SQL Server, MySQL, and DB2:

mysql> SELECT item, serialNumber, SUM(price)
 -> FROM source
 -> GROUP BY item,serialNumber WITH ROLLUP;
+------+--------------+------------+
| item | serialNumber | SUM(price) |
+------+--------------+------------+
| Awl | 1 | 10 |
| Awl | 3 | 10 |
| Awl | NULL | 20 |
| Bowl | 2 | 10 |
| Bowl | 5 | 10 |
| Bowl | 6 | 10 |
| Bowl | NULL | 30 |
| Cowl | 4 | 10 |
| Cowl | NULL | 10 |
| NULL | NULL | 60 |
+------+--------------+------------+

 

5.10.1.2. Oracle and DB2

In Oracle and DB2:

SQL> SELECT item, serialNumber, SUM(price)
 2 FROM source
 3 GROUP BY GROUPING SETS ((item,serialNumber),(item),( ));

ITEM SERIALNUMBER SUM(PRICE)
------------------------------ ------------ ----------
Awl 1 10
Awl 3 10
Awl 20
Bowl 2 10
Bowl 5 10
Bowl 6 10
Bowl 30
Cowl 4 10
Cowl 10
 60

 

5.10.2. Hacking the Hack

Of course, there is no reason why you shouldn't include the GRAND TOTAL in another UNION:

mysql> SELECT item, serialNumber, price FROM(
 -> SELECT item, serialNumber, price FROM source
 -> UNION
 -> SELECT item, NULL, SUM(price)
 -> FROM source
 -> GROUP BY item
 -> UNION
 -> SELECT NULL, NULL, SUM(price)
 -> FROM source
 -> ) t
 -> ORDER BY COALESCE(item,'zzz'), COALESCE(serialNumber,1E9);
+------+--------------+-------+
| item | serialNumber | price |
+------+--------------+-------+
| Awl | 1 | 10 |
| Awl | 3 | 10 |
| Awl | NULL | 20 |
| Bowl | 2 | 10 |
| Bowl | 5 | 10 |
| Bowl | 6 | 10 |
| Bowl | NULL | 30 |
| Cowl | 4 | 10 |
| Cowl | NULL | 10 |
| NULL | NULL | 60 |
+------+--------------+-------+

But having totals showing in the same column as the values themselves can make for a confusing report. If you can format the subtotals and totals so that they stand out, it can help. But it improves readability if you can put these in different columns.

You can add two more columns to the UNION. It takes a little care to put the NULL values in the right place:

mysql> SELECT item,serialNumber,price,sub,grand FROM (
 -> SELECT item,serialNumber,price,NULL AS sub,NULL AS grand
 -> FROM source
 -> UNION
 -> SELECT item,NULL, NULL, SUM(price), NULL
 -> FROM source
 -> GROUP BY item
 -> UNION
 -> SELECT NULL,NULL, NULL, NULL, SUM(price)
 -> FROM source
 -> ) t
 -> ORDER BY COALESCE(item,'zzz'), COALESCE(serialNumber,1E9);
+------+--------------+-------+------+-------+
| item | serialNumber | price | sub | grand |
+------+--------------+-------+------+-------+
| Awl | 1 | 10 | NULL | NULL |
| Awl | 3 | 10 | NULL | NULL |
| Awl | NULL | NULL | 20 | NULL |
| Bowl | 2 | 10 | NULL | NULL |
| Bowl | 5 | 10 | NULL | NULL |
| Bowl | 6 | 10 | NULL | NULL |
| Bowl | NULL | NULL | 30 | NULL |
| Cowl | 4 | 10 | NULL | NULL |
| Cowl | NULL | NULL | 10 | NULL |
| NULL | NULL | NULL | NULL | 60 |
+------+--------------+-------+------+-------+