2 Ways to Return Totals and Subtotals in SQL

When we have a query that returns a column with numerical data such as prices or population, we can use the following methods to automatically calculate the totals and subtotals within a category.

`GROUP BY` with the `WITH ROLLUP` Modifier

One way to calculate totals and subtotals is with the WITH ROLLUP modifier of the GROUP BY clause. When we use this modifier, we get a special row that automatically calculates totals and/or subtotals.

Here’s an example:

SELECT
    IF(GROUPING(CountryCode), 'ALL COUNTRIES', CountryCode) AS CountryCode,
    IF(GROUPING(District), 'ALL DISTRICTS', District) AS District,
    IF(GROUPING(Name), 'ALL CITIES', Name) As CityName,
    SUM(Population) AS Population
FROM City
WHERE CountryCode IN ('AUS', 'NZL')
GROUP BY CountryCode, District, Name WITH ROLLUP;

Result:

+---------------+-----------------+---------------+------------+
| CountryCode   | District        | CityName      | Population |
+---------------+-----------------+---------------+------------+
| AUS           | Capital Region  | Canberra      |     322723 |
| AUS           | Capital Region  | ALL CITIES    |     322723 |
| AUS           | New South Wales | Central Coast |     227657 |
| AUS           | New South Wales | Newcastle     |     270324 |
| AUS           | New South Wales | Sydney        |    3276207 |
| AUS           | New South Wales | Wollongong    |     219761 |
| AUS           | New South Wales | ALL CITIES    |    3993949 |
| AUS           | Queensland      | Brisbane      |    1291117 |
| AUS           | Queensland      | Cairns        |      92273 |
| AUS           | Queensland      | Gold Coast    |     311932 |
| AUS           | Queensland      | Townsville    |     109914 |
| AUS           | Queensland      | ALL CITIES    |    1805236 |
| AUS           | South Australia | Adelaide      |     978100 |
| AUS           | South Australia | ALL CITIES    |     978100 |
| AUS           | Tasmania        | Hobart        |     126118 |
| AUS           | Tasmania        | ALL CITIES    |     126118 |
| AUS           | Victoria        | Geelong       |     125382 |
| AUS           | Victoria        | Melbourne     |    2865329 |
| AUS           | Victoria        | ALL CITIES    |    2990711 |
| AUS           | West Australia  | Perth         |    1096829 |
| AUS           | West Australia  | ALL CITIES    |    1096829 |
| AUS           | ALL DISTRICTS   | ALL CITIES    |   11313666 |
| NZL           | Auckland        | Auckland      |     381800 |
| NZL           | Auckland        | Manukau       |     281800 |
| NZL           | Auckland        | North Shore   |     187700 |
| NZL           | Auckland        | Waitakere     |     170600 |
| NZL           | Auckland        | ALL CITIES    |    1021900 |
| NZL           | Canterbury      | Christchurch  |     324200 |
| NZL           | Canterbury      | ALL CITIES    |     324200 |
| NZL           | Dunedin         | Dunedin       |     119600 |
| NZL           | Dunedin         | ALL CITIES    |     119600 |
| NZL           | Hamilton        | Hamilton      |     117100 |
| NZL           | Hamilton        | ALL CITIES    |     117100 |
| NZL           | Wellington      | Lower Hutt    |      98100 |
| NZL           | Wellington      | Wellington    |     166700 |
| NZL           | Wellington      | ALL CITIES    |     264800 |
| NZL           | ALL DISTRICTS   | ALL CITIES    |    1847600 |
| ALL COUNTRIES | ALL DISTRICTS   | ALL CITIES    |   13161266 |
+---------------+-----------------+---------------+------------+
38 rows in set, 2 warnings (0.03 sec)

Here the WITH ROLLUP modifier provided the numbers for the total and subtotals and we provided the labels.

We provided the labels by using a combination of the MySQL’s IF() function and its GROUPING() function. We specified ALL CITIES and ALL DISTRICTS for the subtotal rows, and ALL COUNTRIES for the grand total at the end.

Note that the labels are optional, and our query could have been a lot simpler without them. If we didn’t add these labels, a NULL value would appear instead of the label (but the numbers would still appear).

See How to Add Totals and Subtotals When Using GROUP BY in MySQL for a more detailed explanation of this approach.

Use a Window Function

Another way to calculate totals and subtotals is with a window function. This option allows us to present the totals and subtotals in their own column. So instead of having extra rows added to the result set for the totals and subtotals, we get extra columns.

Example:

SELECT
    CountryCode AS "cc",
    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",
    SUM(Population)
        OVER (
            ) AS "Total Pop"
FROM City
WHERE CountryCode IN ('AUS', 'NZL')
ORDER BY CountryCode;

Result:

+-----+-----------------+---------------+----------+--------------+-------------+-----------+
| cc  | District        | City Name     | City Pop | District Pop | Country Pop | Total Pop |
+-----+-----------------+---------------+----------+--------------+-------------+-----------+
| AUS | Capital Region  | Canberra      |   322723 |       322723 |    11313666 |  13161266 |
| AUS | New South Wales | Wollongong    |   219761 |      3993949 |    11313666 |  13161266 |
| AUS | New South Wales | Central Coast |   227657 |      3993949 |    11313666 |  13161266 |
| AUS | New South Wales | Newcastle     |   270324 |      3993949 |    11313666 |  13161266 |
| AUS | New South Wales | Sydney        |  3276207 |      3993949 |    11313666 |  13161266 |
| AUS | Queensland      | Cairns        |    92273 |      1805236 |    11313666 |  13161266 |
| AUS | Queensland      | Townsville    |   109914 |      1805236 |    11313666 |  13161266 |
| AUS | Queensland      | Gold Coast    |   311932 |      1805236 |    11313666 |  13161266 |
| AUS | Queensland      | Brisbane      |  1291117 |      1805236 |    11313666 |  13161266 |
| AUS | South Australia | Adelaide      |   978100 |       978100 |    11313666 |  13161266 |
| AUS | Tasmania        | Hobart        |   126118 |       126118 |    11313666 |  13161266 |
| AUS | Victoria        | Geelong       |   125382 |      2990711 |    11313666 |  13161266 |
| AUS | Victoria        | Melbourne     |  2865329 |      2990711 |    11313666 |  13161266 |
| AUS | West Australia  | Perth         |  1096829 |      1096829 |    11313666 |  13161266 |
| NZL | Auckland        | Waitakere     |   170600 |      1021900 |     1847600 |  13161266 |
| NZL | Auckland        | North Shore   |   187700 |      1021900 |     1847600 |  13161266 |
| NZL | Auckland        | Manukau       |   281800 |      1021900 |     1847600 |  13161266 |
| NZL | Auckland        | Auckland      |   381800 |      1021900 |     1847600 |  13161266 |
| NZL | Canterbury      | Christchurch  |   324200 |       324200 |     1847600 |  13161266 |
| NZL | Dunedin         | Dunedin       |   119600 |       119600 |     1847600 |  13161266 |
| NZL | Hamilton        | Hamilton      |   117100 |       117100 |     1847600 |  13161266 |
| NZL | Wellington      | Lower Hutt    |    98100 |       264800 |     1847600 |  13161266 |
| NZL | Wellington      | Wellington    |   166700 |       264800 |     1847600 |  13161266 |
+-----+-----------------+---------------+----------+--------------+-------------+-----------+
23 rows in set (0.00 sec)

When we use this method, we get duplicate values in the various columns, due to the fact that the countries and most districts contain multiple cities. In those cases where a district contains just one city, we don’t get any duplicates for the district column.

GROUP BY with the WITH ROLLUP Modifier

Use a Window Function

`GROUP BY` with the `WITH ROLLUP` Modifier