PostgreSQL: Use Case Statement with Multiple Conditions

Often in PostgreSQL you may want to use a CASE WHEN statement with multiple conditions.

You can use the following syntax to do so:

SELECT *,
 CASE
  WHEN (team = 'Mavs' AND role = 'Guard') THEN 'MG'
  WHEN (team = 'Mavs' AND role = 'Forward') THEN 'MF'
  WHEN (team = 'Hawks' AND role = 'Guard') THEN 'HG'
  WHEN (team = 'Hawks' AND role = 'Forward') THEN 'HF'
  ELSE 'None'
 END AS team_role
FROM athletes;

This particular example uses a CASE WHEN statement to create a new column named team_role that takes on the following values:

• MG when the team column is Mavs and the role column is Guard.
• MF when the team column is Mavs and the role column is Forward.
• HG when the team column is Hawks and the role column is Guard.
• HF when the team column is Hawks and the role column is Forward.
• None if none of the previous conditions are met.

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

Example: How to Use Case Statement with Multiple Conditions 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,
  role TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 'Guard', 15);
INSERT INTO athletes VALUES (0002, 'Mavs', 'Guard', 12);
INSERT INTO athletes VALUES (0003, 'Mavs', 'Forward', 23);
INSERT INTO athletes VALUES (0004, 'Mavs', 'Forward', 40);
INSERT INTO athletes VALUES (0005, 'Hawks', 'Guard', 29);
INSERT INTO athletes VALUES (0006, 'Hawks', 'Forward', 14);
INSERT INTO athletes VALUES (0007, 'Hawks', 'Forward', 33);
INSERT INTO athletes VALUES (0008, 'Hawks', 'Forward', 12);

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

Suppose that we would like to use a case statement to create a new column named team_role that returns abbreviations that are based on the existing values in both the team and role columns.

We can use the following syntax to do so:

SELECT *,
 CASE
  WHEN (team = 'Mavs' AND role = 'Guard') THEN 'MG'
  WHEN (team = 'Mavs' AND role = 'Forward') THEN 'MF'
  WHEN (team = 'Hawks' AND role = 'Guard') THEN 'HG'
  WHEN (team = 'Hawks' AND role = 'Forward') THEN 'HF'
  ELSE 'None'
 END AS team_role
FROM athletes;

 athleteid | team  |  role   | points | team_role 
-----------+-------+---------+--------+-----------
         1 | Mavs  | Guard   |     15 | MG
         2 | Mavs  | Guard   |     12 | MG
         3 | Mavs  | Forward |     23 | MF
         4 | Mavs  | Forward |     40 | MF
         5 | Hawks | Guard   |     29 | HG
         6 | Hawks | Forward |     14 | HF
         7 | Hawks | Forward |     33 | HF
         8 | Hawks | Forward |     12 | HF

We can see that the resulting table contains a new column named team_role whose values are dependent on the existing values in the team and role columns.

Feel free to use similar syntax to use a CASE WHEN statement with multiple conditions in your own table in PostgreSQL.

Additional Resources

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

PostgreSQL: How to Count Occurrences of Each Value in Column
PostgreSQL: How to Count Number of Occurrences of Character in String
PostgreSQL: How to Count True and False Values