With Rollup

Background
SQL does not normally put a total line in the same result set as the detail like an accounting spreadsheet. By adding with rollup to the group by clause you will get the total line as shown below. 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 rollup 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 column where the summary row has been inserted.

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

Example 1
Show the animals available by store with subtotal for each animal type and a grand total.

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.

Combination Queries

There is a set of queries sometimes referred to as combination queries. There are three set operators used to create them...

• UNION
• INTERSECT
• EXCEPT

We looked at the Union in our basic class. Below we will present the other two. Each is based on the bookstore database which can be downloaded here.

INTERSECT
The intersect operator retrieves all unique rows found in both tables.

Example
The following query will find all cities in common between the sources and customers tables.

Script
use bookstore -- MySQL and SQL Server
select customer_city, customer_state, customer_zip
from customers
intersect
select source_city, source_state, source_zip
from sources;

EXCEPT
The except operator will retrieve all rows found in the first table but not in the second.

Example
Find all cities where the source is located in a city that does not have a customer.

Script
use bookstore -- MySQL and SQL Server
select source_city, source_state, source_zip
from sources
except
select customer_city, customer_state, customer_zip
from customers;