How to add Total in last row of SQL SUM

Sometimes, you need to report the result of some arithmetic SQL operation in your database, along with the total. In this article, you will see how to add a Total row at the end of query result with ROLLUP() for SUM() operation.

Set up the data

Let imagine we have a table of movies. So you can create your table with this code.

DROP TABLE IF EXISTS movies;
CREATE TABLE movies (
    id SERIAL PRIMARY KEY,
    title VARCHAR NOT NULL,
    release_year VARCHAR,
    genre genre,
    price NUMERIC(4, 2)
);

Load the data

You can fill your table with this data.

INSERT INTO movies(id, title, release_year, genre, price)
VALUES
    (1, 'The Shaw shank Redemption', '1994', 'HORROR', 15.99),
    (2, 'Ant Man', '2019', 'ADVENTURE', 15.00),
    (3, 'Fallen', '1994', 'HORROR', 23.99),
    (4, 'The barbershop', '2006', 'COMEDY', 6.50),
    (5, 'The last dance', '2021', 'SPORTS', 55.99),
    (6, 'Peter Pan', '2006', 'ADVENTURE', 15.99),
    (7, 'Fast & Furious 7', '2019', 'ACTION', 36.00),
    (8, 'Harry Potter', '2000', 'ACTION', 26.50),
    (9, 'Jungle book', '2004', 'ADVENTURE', 25.00);

Then you can use a simple select query to retrieve data.

SELECT * FROM movies;

Sum Operation

Let’s say you have a use case that tells you to sum the price of movies grouped by release year. To do so, you will run this command.

SELECT release_year, SUM(price)
FROM movies
GROUP BY release_year;

Adding total row at the end

This is code to add the total row at the end of the result.

SELECT
    CASE
        WHEN GROUPING(release_year) = 1 THEN 'Total'
        ELSE release_year
    END AS "Release year",
    SUM(price) AS "Prices"
FROM
    movies
GROUP BY
    ROLLUP (release_year)
ORDER BY
    CASE
        WHEN GROUPING(release_year) = 1 THEN NULL
        ELSE release_year
    END NULLS LAST;

You can found all those code snippets here on my GitHub.