How Can I Efficiently Count Conditional Occurrences in SQL Queries?

Efficiently Counting Conditional Occurrences in SQL

This guide explores various methods for counting conditional data occurrences within SQL queries, grouped by a specific column. The optimal approach depends on your database system.

PostgreSQL 9.4 and Later: FILTER Clause

PostgreSQL 9.4 and subsequent versions offer the most efficient and readable solution using the FILTER clause within the COUNT aggregate function:

<code class="language-sql">SELECT category,
       COUNT(*) FILTER (WHERE question1 = 0) AS zero,
       COUNT(*) FILTER (WHERE question1 = 1) AS one,
       COUNT(*) FILTER (WHERE question1 = 2) AS two
FROM reviews
GROUP BY category;</code>

Concise Alternative for PostgreSQL

A shorter syntax achieves the same result:

<code class="language-sql">SELECT category,
       COUNT(CASE WHEN question1 = 0 THEN 1 END) AS zero,
       COUNT(CASE WHEN question1 = 1 THEN 1 END) AS one,
       COUNT(CASE WHEN question1 = 2 THEN 1 END) AS two
FROM reviews
GROUP BY category;</code>

Optimal Crosstab for Extensive Data

For comprehensive crosstab queries, especially with numerous categories, the crosstab() function provides superior performance:

<code class="language-sql">
SELECT *
FROM crosstab(
    'SELECT category, question1, COUNT(*) AS ct
     FROM reviews
     GROUP BY 1, 2
     ORDER BY 1, 2',
    'VALUES (0), (1), (2)'
) AS ct (category TEXT, zero INT, one INT, two INT);
```  This approach dynamically handles a larger number of categories more efficiently than the previous methods.</code>

The above is the detailed content of How Can I Efficiently Count Conditional Occurrences in SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!