How Can I Efficiently Count Conditional Occurrences in PostgreSQL?

PostgreSQL condition counting: optimization method

When counting the occurrences of data in a database table, conditional statements may appear lengthy. Fortunately, PostgreSQL provides a convenient and efficient solution.

FILTER aggregation options

For PostgreSQL 9.4 and later, take advantage of the FILTER aggregation option for best efficiency:

<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>

Concise syntax

If simplicity is your priority:

<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>

Crosstab query

For handling large option lists and pursuing higher performance, consider using the crosstab() function:

<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>

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