PostgreSQL GROUP BY Statement

In PostgreSQL, the GROUP BY statement is used in conjunction with the SELECT statement to group data with identical values.

The GROUP BY clause is placed in a SELECT statement after the WHERE clause and before the ORDER BY clause.

Syntax

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

The GROUP BY clause must come after the conditions in the WHERE clause and must precede the ORDER BY clause.

In the GROUP BY clause, you can group by one or multiple columns, but the grouped columns must exist in the column list.

Example

Create a COMPANY table (download COMPANY SQL file), with the following data:

tutorialprodb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

The following example groups the data by the NAME field to find the total salary for each person:

tutorialprodb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

This results in:

name  |  sum
 -------+-------
  Teddy | 20000
  Paul  | 20000
  Mark  | 65000
  David | 85000
  Allen | 15000
  Kim   | 45000
  James | 10000
(7 rows)

Now, let's add three records to the COMPANY table using the following statements:

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

Now the COMPANY table contains duplicate names, with the data as follows:

id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

Now, let's group by the NAME field again to find the total salary for each customer:

tutorialprodb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

The resulting output is as follows:

name | sum -------+------- Allen | 15000 David | 85000 James | 20000 Kim | 45000 Mark | 65000 Paul | 40000 Teddy | 20000 (7 rows)

The following example uses the ORDER BY clause with the GROUP BY clause:

tutorialprodb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

This yields the following result:

name | sum -------+------- Teddy | 20000 Paul | 40000 Mark | 65000 Kim | 45000 James | 20000 David | 85000 Allen | 15000 (7 rows) ```