How To Use UNION to Add a Total Line to a SQL Query

I searched and searched for a way to add a total line to a report using SQL. I needed a way to provide a single total line to a report that sums total projects per employee.

The easiest way was to add 'with rollup' to the group clause:

select Office, Employee, count(*)
from Projects
group by Office, Employee with rollup

Unfortunately, this adds to a sub-total to each office, which is not desired.

Then I discovered UNION while searching for another solution. UNION combines two tables with like data.

select Office, Employee, count(*)
from Projects
group by Office, Employee
UNION
select 'Total', '', count(*)
from Projects

Office	Employee	Projects
Louisville	Mike Campbell	7
Anywhere	John Doe	3
Total		10