How Can I Efficiently Count Data Occurrences in SQL with Multiple Conditions?

Efficient SQL condition counting method

In SQL, there are many ways to group and count the occurrences of data according to a specific column. In PostgreSQL, the most straightforward way is to use a CASE expression, as shown in the example query. However, this approach can become cumbersome when dealing with a large number of possible values.

PostgreSQL 9.4 and later provides a more optimized and flexible method: the FILTERaggregation option. The FILTER clause allows you to apply additional conditions to an aggregate function, counting only specific values ​​that match a specific condition.

<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 1;</code>

This query effectively counts the occurrences of zero, one, and two in the question1 column, grouped by the category column.

For further optimization, the following short version can be used:

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

For complex scenarios involving multiple conditions or a large number of options, the crosstab() function provides excellent performance and simplicity:

<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);</code>

This crosstab query produces a result set containing all required counts, presented as named columns for easy access and analysis.

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