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.

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 ORDERBY 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 ORDERBY applies to the whole UNION:

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: