Using a Union to Add a Grand Total

It is possible to add a grand total line to a query in a couple of ways: using the WITH ROLLUP clause and using a UNION. I will cover the latter here and the former in another post.

Example
Show the total quantity ordered and the total cost for the orders in the bookstore database. Add to this a total line showing the total quantities and costs for the entire orderlines table. In other words present the detail and the summary.

Approach
Take the subtotal as you normally would using a group by with aggregate functions in the select list. Then union the results with another select without a group by clause. This latter select will need an additional column to make it union compatible with the first select. The number of columns has to be the same. You can use a null which is what the rollup does.

Script
use bookstore; -- MySQL and SQL Server
select order_numb
, sum(quantity) as "Quantity"
, sum(cost_line)as "Total Cost"
from orderlines
group by order_numb
union
select NULL, sum(quantity), sum(cost_line)
from orderlines;
Results

The following is part of the result set showing the total line with a null in the first column.