With Cube

Background
SQL does not normally put total lines in the same result set as the detail like an accounting spreadsheet. By adding with cube to the group by clause you will get the total lines as shown below. It is similar to with rollup but subtotals each dimension. For our examples we will use a tiny fact table from the pets database. You can download the database script here. It is a tiny fact table that summarizes animals available by store. We originally obtained the table from the internet, I forget where. At some point we may add dimension tables to get a true star schema.

Notes
The result set returned by a group by with cube is a valid SQL result set. It can be filtered or manipulated like any other result set. The total line or lines returned are not the total of the groups shown. They are the totals in the table. An example below will make this clearer. A NULL is placed in the dimension column where a summary row has been inserted.

Syntax
SELECT statement...
...
GROUP BY groupbylist
WITH CUBE

Example 1
Show the animals available by store with subtotals for each store type and a grand total. In addition show the subtotals for each animal type.

Example 2
Same above but retain only rows with number available greater than 10. Note that the grand total row still shows 63 animals available.