PostgreSQL: How to Use INNER JOIN with GROUP BY

Often in PostgreSQL you may want to use the INNER JOIN statement with a GROUP BY clause to join two tables together and then group the output by a specific column.

You can use the following basic syntax to do so:

SELECT team.name, SUM(points.amount)
FROM team
INNER JOIN points
ON team.id = points.id
GROUP BY team.name;

This particular example will perform an inner join on the team and points tables by joining on matching values in the id column of each table.

The output will display the results of the inner join, grouped by the name column of the team table.

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

Example: How to Use INNER JOIN with GROUP BY in PostgreSQL

First, we will create the following table named team that contains information about the team names of various basketball teams:

-- create table named 'team'
CREATE TABLE team (
  id INTEGER,
  name TEXT
);

-- add rows to the table 
INSERT INTO team VALUES (1, 'Mavs');
INSERT INTO team VALUES (2, 'Celtics');
INSERT INTO team VALUES (3, 'Kings');

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

 id |  name   
----+---------
  1 | Mavs
  2 | Celtics
  3 | Kings

Next, we will create the following table named points that contains information about the points scored by basketball players on various teams:

-- create table named 'points'
CREATE TABLE points (
  id INTEGER,
  amount INTEGER
);

-- add rows to the table 
INSERT INTO points VALUES (1, 24);
INSERT INTO points VALUES (1, 16);
INSERT INTO points VALUES (2, 35);
INSERT INTO points VALUES (2, 20);
INSERT INTO points VALUES (3, 17);
INSERT INTO points VALUES (3, 15);

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

 id | amount 
----+--------
  1 |     24
  1 |     16
  2 |     35
  2 |     20
  3 |     17
  3 |     15

We can use the following syntax with an INNER JOIN statement along with a GROUP BY clause to join these tables together by matching on values from the id column in each table, then grouping the results by values from the name column of the team table:

SELECT team.name, SUM(points.amount)
FROM team
INNER JOIN points
ON team.id = points.id
GROUP BY team.name;

  name   | sum 
---------+-----
 Kings   |  32
 Mavs    |  40
 Celtics |  55

The resulting table displays the sum of points scored by players on each team.

You can use similar syntax with the INNER JOIN statement and the GROUP BY clause to join two tables and group the results in PostgreSQL.

Additional Resources

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

PostgreSQL: How to Use LEFT JOIN on Multiple Tables
PostgreSQL: How to SELECT DISTINCT on Multiple Columns
PostgreSQL: Use COUNT DISTINCT with CASE WHEN