Using a UNION Query to display the raw data as well as summary information

Using a UNION Query to display the raw data as well as summary information

Usually, in a properly normalised database, there is no reason to use a UNION Query. However, if you want to display (not necessarily in a report) data as well as various levels of grouping, then a UNION Query may be just what you need. I use this method when I am exporting data to another program, and want to include various totals with the data.

Consider the Product Sales for 1995 query in the Northwind database. As well as listing the Product Sales, you may also want to include within the same query the Category Sales and also the total sales.

You can create an SQL statement that looks something like:

SELECT
    ProductName AS Products,
    " " & CategoryName AS Categories,
    ProductSales AS Sales,
    1 AS QueryOrder
FROM
    [Product Sales for 1995]
UNION SELECT
    "All Products within " & CategoryName AS Products,
    " " & CategoryName AS Categories,
    Sum(ProductSales) AS Sales,
    2 AS QueryOrder
FROM
    [Product Sales for 1995]
GROUP BY
    CategoryName
UNION SELECT
    "All Products" AS Products,
    "All Categories" AS Categories,
    Sum(ProductSales) AS Sales,
    3 AS QueryOrder
FROM
    [Product Sales for 1995]
ORDER BY
    Categories,
    QueryOrder,
    Products
;

I prefer to write the entire SQL statement as one query, but you could just as well create as many queries as you require, and then use them in the UNION Query.

Also, another thing to note is that in the above example, I have forced the overall total to be at the end by preceeding the Product and Category totals with a space.

Top

Last modified at 06/06/2006 14:58:55