PostgreSQL: How to Add Total Row in Query

Often in PostgreSQL you may want to add a ‘total’ row in a grouped query.

You can use the ROLLUP() function with the following syntax to do so:

SELECT team, sum(points) AS total
FROM athletes 
GROUP BY ROLLUP(team);

This particular query calculates the sum of values in the points column of the athletes table, grouped by the values in the team column.

We then use the ROLLUP() function to add a row to the output that displays the grand total of values in the points column.

The following example shows how to use this syntax in practice.

Example: How to Add Total Row to Query in PostgreSQL

Suppose that we create the following table named athletes that contains information about points scored by various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 30);
INSERT INTO athletes VALUES (0003, 'Mavs', 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 14);
INSERT INTO athletes VALUES (0006, 'Heat', 16);
INSERT INTO athletes VALUES (0007, 'Heat', 40);
INSERT INTO athletes VALUES (0008, 'Heat', 18);
INSERT INTO athletes VALUES (0009, 'Heat', 16);
INSERT INTO athletes VALUES (0010, 'Heat', 33);

-- view all rows in table
SELECT * FROM athletes;

 id | team | points 
----+------+--------
  1 | Mavs |     22
  2 | Mavs |     30
  3 | Mavs |     12
  4 | Mavs |      9
  5 | Mavs |     14
  6 | Heat |     16
  7 | Heat |     40
  8 | Heat |     18
  9 | Heat |     16
 10 | Heat |     33

Suppose that we would like to calculate the sum of points scored by each team along with a grand total of points scored by all players.

We can use the ROLLUP() function with the following syntax to do so:

SELECT team, sum(points) AS total
FROM athletes 
GROUP BY ROLLUP(team);

 team | total 
------+-------
      |   210
 Heat |   123
 Mavs |    87

Notice that the output displays the sum of points for each team along with the grand total of points scored by all players.

Note that you can also use an ORDER BY clause to place the ‘total’ row at the bottom of the output:

SELECT team, sum(points) AS total
FROM athletes 
GROUP BY ROLLUP(team)
ORDER BY team;

 team | total 
------+-------
 Heat |   123
 Mavs |    87
      |   210

The ‘total’ row is now displayed last.

You can use similar syntax to add a ‘total’ row to your own query in PostgreSQL.

Note: You can find the complete documentation for the ROLLUP() function in PostgreSQL here.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Top N Percent of Records
PostgreSQL: How to Calculate a Moving Average
PostgreSQL: How to Calculate Average Across Multiple Columns