PostgreSQL sum() Function

The PostgreSQL sum() function is an aggregate function that computes the sum of all specified values ​​in a group and returns the result.

sum() Syntax

Here is the syntax of the PostgreSQL sum() function:

sum(expr)

Typically, we use the sum() function like:

SELECT sum(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

expr

Required. A column name or expression that computes the sum.

Return value

The PostgreSQL sum() function returns the sum of all specified values ​​in a group.

sum() Examples

To demonstrate usages of the PostgreSQL sum() function, we simulate a temporary table using the following statement with UNION and SELECT:

SELECT 'Tim' name, 'Math' subject, 8 grade
UNION
SELECT 'Tim' name, 'English' subject, 9 grade
UNION
SELECT 'Tom' name, 'Math' subject, 7 grade
UNION
SELECT 'Tom' name, 'English' subject, 5 grade;

 name | subject | grade
------+---------+-------
 Tim  | English |     9
 Tom  | Math    |     7
 Tim  | Math    |     8
 Tom  | English |     5
(4 rows)

Here, we have some rows for user grades. The name column is the name of the user, the subject column is the name of the subject, and the grade column is the grade of a subject.

If you want to get the total grade, use the following statement with the sum() function:

SELECT
  sum(t.grade) sum_grade
FROM
  (
    SELECT 'Tim' name, 'Math' subject, 8 grade
    UNION
    SELECT 'Tim' name, 'English' subject, 9 grade
    UNION
    SELECT 'Tom' name, 'Math' subject, 7 grade
    UNION
    SELECT 'Tom' name, 'English' subject, 5 grade
  ) t;

 sum_grade
-----------
        29
(1 row)

If you want to get the total grade for everyone, use the following statement with the sum() function:

SELECT
  t.name,
  sum(t.grade) sum_grade
FROM
  (
    SELECT 'Tim' name, 'Math' subject, 8 grade
    UNION
    SELECT 'Tim' name, 'English' subject, 9 grade
    UNION
    SELECT 'Tom' name, 'Math' subject, 7 grade
    UNION
    SELECT 'Tom' name, 'English' subject, 5 grade
  ) t
GROUP BY t.name;

 name | sum_grade
------+-----------
 Tim  |        17
 Tom  |        12
(2 rows)

Here, we use the GROUP BY t.name clause to group all rows by the user’s name, and use the sum(t.grade) function to sum the grades in each group.