How Can I Simplify Aggregating Game Statistics with Distinct Filters in PostgreSQL?

Aggregating columns using additional (different) filters

The following code counts the number of games played, won and lost by each player. However, its length and complexity pose challenges. How can we simplify this query?

PostgreSQL 9.4 or higher

Use the FILTER clause to filter aggregations based on a Boolean expression:

<code class="language-sql">SELECT u.name,
       COUNT(*) FILTER (WHERE g.winner_id > 0) AS played,
       COUNT(*) FILTER (WHERE g.winner_id = u.id) AS won,
       COUNT(*) FILTER (WHERE g.winner_id <> u.id) AS lost
FROM   games g
JOIN   users u ON u.id IN (g.player_1_id, g.player_2_id)
GROUP  BY u.name;</code>

Any PostgreSQL version

Emphasis PostgreSQL 9.4 introduced the FILTER clause. For older versions, a workaround exists:

<code class="language-sql">SELECT u.name,
       COUNT(CASE WHEN g.winner_id > 0 THEN 1 END) AS played,
       COUNT(CASE WHEN g.winner_id = u.id THEN 1 END) AS won,
       COUNT(CASE WHEN g.winner_id <> u.id THEN 1 END) AS lost
FROM   games g
JOIN   users u ON u.id IN (g.player_1_id, g.player_2_id)
GROUP  BY u.name;</code>

Note: The solution for older versions of PostgreSQL uses the CASE statement instead of the FILTER clause to achieve the same effect. Both methods accurately calculate a player's game statistics, but the FILTER clause has the advantage in terms of readability and maintainability.

The above is the detailed content of How Can I Simplify Aggregating Game Statistics with Distinct Filters in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!