Returning Aggregate Data in the Same Recordset as Individual Records

This week's tip expands on one I wrote in May, titled Fun with Order By.

I have the following table, called "tblWidgetsSold":

Following the advice I gave in my previous tip, I have come up with the following query, which will return a list of the number of widgets sold in each country, starting with New Zealand, and then followed by the rest of the world, with those countries with the highest number of widgets sold appearing first:

SELECT Country, WidgetsSold
FROM tblWidgetsSold
ORDER BY CASE WHEN Country='New Zealand'
THEN 0 ELSE 1 END,
WidgetsSold DESC

This week, I have taken my New-Zealand-centric view a step further, by aggregating the sales figures from the rest of the world:

SELECT CASE WHEN Country='New Zealand' THEN 'New Zealand'
ELSE 'Rest of the World' END AS Region,
SUM(WidgetsSold) AS WidgetsSold
FROM tblWidgetsSold
GROUP BY CASE WHEN Country='New Zealand' THEN 'New Zealand'
ELSE 'Rest of the World' END

The output from this query is:

What I would like to do now, is include a grand total figure in the output.

One way to accomplish this, is with a UNION query, as follows:

SELECT CASE WHEN Country='New Zealand' THEN 'New Zealand'
ELSE 'Rest of the World' END AS Region,
SUM(WidgetsSold) AS WidgetsSold
FROM tblWidgetsSold
GROUP BY CASE WHEN Country='New Zealand' THEN 'New Zealand'
ELSE 'Rest of the World' END
UNION
SELECT 'Total' AS Region, SUM(WidgetsSold) AS WidgetsSold
FROM tblWidgetsSold
ORDER BY Region

This query will return the following 3 rows:

In SQL server, it is also possible to retrieve a similar recordset using the CUBE operator:

SELECT CASE WHEN Country='New Zealand' THEN 'New Zealand'
ELSE 'Rest of the World' END AS Region,
SUM(WidgetsSold) AS WidgetsSold
FROM tblWidgetsSold
GROUP BY CASE WHEN Country='New Zealand' THEN 'New Zealand'
ELSE 'Rest of the World' END WITH CUBE

This query will return the following 3 rows:

In many ways, the CUBE syntax is a lot neater, but the alternative method, using a UNION, is handy when you don't have a CUBE operator.

Adelle.