PostgreSQL: How to Calculate Percent of Total in Column

Often in PostgreSQL you may want to calculate the percent of a total in a column.

You can use the following basic syntax to do so:

SELECT
    team,
    (COUNT(*) / (SUM(COUNT(*)) OVER() )) * 100 AS team_pct
FROM
    athletes
GROUP BY 
    team;

This particular example will calculate the percentage of total values that each unique value in the team column represents.

The results will be saved to a new column named team_pct.

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

Example: How to Calculate Percent of Total in Column in PostgreSQL

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

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

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Hawks', 10, 5);
INSERT INTO athletes VALUES (0008, 'Celtics', 14, 13);
INSERT INTO athletes VALUES (0009, 'Hawks', 20, 8);
INSERT INTO athletes VALUES (0010, 'Hawks', 26, 3);

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

 athleteid |  team   | points | assists 
-----------+---------+--------+---------
         1 | Mavs    |     22 |       5
         2 | Hawks   |     30 |      12
         3 | Kings   |     12 |       3
         4 | Kings   |     25 |       9
         5 | Mavs    |     31 |      14
         6 | Mavs    |     16 |       3
         7 | Hawks   |     10 |       5
         8 | Celtics |     14 |      13
         9 | Hawks   |     20 |       8
        10 | Hawks   |     26 |       3

Suppose that we would like to calculate the percentage of occurrences of each value in the team column relative to the total occurrences.

We can use the following syntax to do so:

SELECT
    team,
    (COUNT(*) / (SUM(COUNT(*)) OVER() )) * 100 AS team_pct
FROM
    athletes
GROUP BY 
    team;

  team   |        team_pct         
---------+-------------------------
 Kings   | 20.00000000000000000000
 Mavs    | 30.00000000000000000000
 Hawks   | 40.00000000000000000000
 Celtics | 10.00000000000000000000

The new column named team_pct shows the percentage of the total that each value in the team column represents.

For example, we can see:

• The team name Kings represents 20% of all values in the team column.
• The team name Mavs represents 30% of all values in the team column.
• The team name Hawks represents 40% of all values in the team column.
• The team name Celtics represents 10% of all values in the team column.

Note that the total percentages add up to 100%.

You can use similar syntax to calculate the percentage of a total in a column of your own table.

Additional Resources

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

PostgreSQL: How to Calculate a Rolling Sum
PostgreSQL: How to Calculate Sum by Group
PostgreSQL: How to Calculate Sum of Multiple Columns