When we use an OVER() clause to create a window function in SQL, we often use a PARTITION BY clause to partition the results. This can be handy if we want to do stuff like calculate subtotals.
But we can also use an empty OVER clause to calculate a grand total.
We might want to do this if we’re already using another OVER clause to calculate subtotals, but we also want a column to provide the grand total.
Example
Here’s an example of using two window functions; one to calculate subtotals, and another to calculate the grand total.
We’ll use the world sample database, which contains data such as countries, districts, cities, and city population.
This example was done in MySQL, but it should work in most major RDBMSs. That said, you should check your RDBMS’s documentation for window functions in case it uses a different syntax.
Sample Data
We have a table called City with the following data:
SELECT * FROM City
WHERE CountryCode = 'AUS';Result:
+-----+---------------+-------------+-----------------+------------+ | ID | Name | CountryCode | District | Population | +-----+---------------+-------------+-----------------+------------+ | 130 | Sydney | AUS | New South Wales | 3276207 | | 131 | Melbourne | AUS | Victoria | 2865329 | | 132 | Brisbane | AUS | Queensland | 1291117 | | 133 | Perth | AUS | West Australia | 1096829 | | 134 | Adelaide | AUS | South Australia | 978100 | | 135 | Canberra | AUS | Capital Region | 322723 | | 136 | Gold Coast | AUS | Queensland | 311932 | | 137 | Newcastle | AUS | New South Wales | 270324 | | 138 | Central Coast | AUS | New South Wales | 227657 | | 139 | Wollongong | AUS | New South Wales | 219761 | | 140 | Hobart | AUS | Tasmania | 126118 | | 141 | Geelong | AUS | Victoria | 125382 | | 142 | Townsville | AUS | Queensland | 109914 | | 143 | Cairns | AUS | Queensland | 92273 | +-----+---------------+-------------+-----------------+------------+ 14 rows in set (0.00 sec)
This table contains city names and their populations, as well as the district and country (country code) that each city belongs to.
Note that these populations don’t reflect the actual populations in those cities. Nor does it represent all cities in the respective districts. This is just a sample database with sample data.
Calculate the Grand Total (and Subtotals)
Here’s how we can use a window function to calculate a grand total for the above query, as well as another one to calculate subtotals:
SELECT
District,
Name AS "City Name",
Population AS "City Pop",
SUM(Population)
OVER (
PARTITION BY District
ORDER BY Population
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS "District Pop",
SUM(Population)
OVER (
) AS "Country Pop"
FROM City
WHERE CountryCode = 'AUS';Result:
+-----------------+---------------+----------+--------------+-------------+ | District | City Name | City Pop | District Pop | Country Pop | +-----------------+---------------+----------+--------------+-------------+ | Capital Region | Canberra | 322723 | 322723 | 11313666 | | New South Wales | Wollongong | 219761 | 3993949 | 11313666 | | New South Wales | Central Coast | 227657 | 3993949 | 11313666 | | New South Wales | Newcastle | 270324 | 3993949 | 11313666 | | New South Wales | Sydney | 3276207 | 3993949 | 11313666 | | Queensland | Cairns | 92273 | 1805236 | 11313666 | | Queensland | Townsville | 109914 | 1805236 | 11313666 | | Queensland | Gold Coast | 311932 | 1805236 | 11313666 | | Queensland | Brisbane | 1291117 | 1805236 | 11313666 | | South Australia | Adelaide | 978100 | 978100 | 11313666 | | Tasmania | Hobart | 126118 | 126118 | 11313666 | | Victoria | Geelong | 125382 | 2990711 | 11313666 | | Victoria | Melbourne | 2865329 | 2990711 | 11313666 | | West Australia | Perth | 1096829 | 1096829 | 11313666 | +-----------------+---------------+----------+--------------+-------------+ 14 rows in set (0.00 sec)
In this case, the grand total is in the Country Pop column. The subtotals are in the District Pop column.
Both columns use data from the Population column, which holds the population of each city. We used the SUM() function to calculate the subtotals and grand total, based on the values in this column.
When we calculate the grand total, we get the same value across all rows. This is the way window functions work. We get this because of the granularity of the query. We want to see each city in its own row, and so therefore subtotals and grand total values will be repeated whenever there’s more than one city within a partition. The District Pop column is partitioned by district. We didn’t specify a partition for the Country Pop column and so it calculates its value based on the whole result set.
However, that’s not to say that an empty OVER clause is the only way to do it. We could have explicitly partitioned the column by the CountryCode column to achieve the same result:
SELECT
District,
Name AS "City Name",
Population AS "City Pop",
SUM(Population)
OVER (
PARTITION BY District
ORDER BY Population
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS "District Pop",
SUM(Population)
OVER (
PARTITION BY CountryCode
) AS "Country Pop"
FROM City
WHERE CountryCode = 'AUS';Result:
+-----------------+---------------+----------+--------------+-------------+ | District | City Name | City Pop | District Pop | Country Pop | +-----------------+---------------+----------+--------------+-------------+ | Capital Region | Canberra | 322723 | 322723 | 11313666 | | New South Wales | Wollongong | 219761 | 3993949 | 11313666 | | New South Wales | Central Coast | 227657 | 3993949 | 11313666 | | New South Wales | Newcastle | 270324 | 3993949 | 11313666 | | New South Wales | Sydney | 3276207 | 3993949 | 11313666 | | Queensland | Cairns | 92273 | 1805236 | 11313666 | | Queensland | Townsville | 109914 | 1805236 | 11313666 | | Queensland | Gold Coast | 311932 | 1805236 | 11313666 | | Queensland | Brisbane | 1291117 | 1805236 | 11313666 | | South Australia | Adelaide | 978100 | 978100 | 11313666 | | Tasmania | Hobart | 126118 | 126118 | 11313666 | | Victoria | Geelong | 125382 | 2990711 | 11313666 | | Victoria | Melbourne | 2865329 | 2990711 | 11313666 | | West Australia | Perth | 1096829 | 1096829 | 11313666 | +-----------------+---------------+----------+--------------+-------------+ 14 rows in set (0.00 sec)
An Alternative
Some RDBMSs allow a WITH ROLLUP modifier to be attached to the GROUP BY clause clause. This provides us with an alternative way to get grand totals and subtotals in our result set. See How to Add Totals and Subtotals When Using GROUP BY in MySQL for examples.