How to count how many rows inside a "group by" group meets a certain criteria

Question

I have a table I wish to select data from by grouping the data by using a certain key. For each group I would also like to count how many rows belonging to the group meets a certain criteria. Even if 0 row from that group meets the criteria, I still want to return this group and have a "Count" field display that 0 rows meets the criteria. Because of this, I can't simply filter out undesired rows with a "where" clause and simply select the count of the number of elements inside the group.

Any help would be greatly appreciated

Answer 1

I would suggest using CASE WHEN (standard ISO SQL syntax) like in this example:

SELECT   a.category,
         SUM(CASE WHEN a.is_interesting = 1 THEN 1 END) AS conditional_count,
         COUNT(*) group_count
FROM     a
GROUP BY a.category

This will sum up values of 1 and null values (when the condition is false), which comes down to actually counting the records that meet the condition.

This will however return null when no records meet the conditions. If you want to have 0 in that case, you can either wrap the SUM like this:

COALESCE(SUM(CASE WHEN a.is_interesting = 1 THEN 1 END), 0)

or, shorter, use COUNT instead of SUM:

COUNT(CASE WHEN a.is_interesting = 1 THEN 1 END)

For COUNT it does not matter what value you put in the THEN clause, as long as it is not null. It will count the instances where the expression is not null.

The addition of the ELSE 0 clause also generally returns 0 with SUM:

SUM(CASE WHEN a.is_interesting = 1 THEN 1 ELSE 0 END)

There is however one boundary case where that SUM will still return null. This is when there is no GROUP BY clause and no records meet the WHERE clause. For instance:

SELECT SUM(CASE WHEN 1 = 1 THEN 1 ELSE 0 END)
FROM   a
WHERE  1 = 0

will return null, while the COUNT or COALESCE versions will still return 0.

Answer 2

You have a condition on which you GROUP BY. Simply add a column with a conditional expression that can be summed up:

..., SUM(
   CASE WHEN othercondition THEN 1
        ELSE 0 END
) AS MatchingCondition, ...

The rows for which the condition is true will yield 1, thereby appearing as a row count in the grouped results. If no rows match, you will get NULL, so you need to wrap the SUM in a COALESCE to reduce it to the desired value of 0.

Derived trick

This offers a way to group for different conditions. Say that you have three different conditions which are mutually exclusive, and you want to count all three (i.e., in a GROUP BY tuple you have 32 rows of which 10 match condition 1, 6 match condition 2 and 16 match condition 3). If you additionally know that the maximum number of tuples in a group will never exceed N, you can encode the three conditions in a single number:

..., SUM(
   CASE WHEN condition1 THEN 1
        WHEN condition2 THEN N
        WHEN condition3 THEN N*N
        WHEN condition4 THEN N*N*N
        ELSE 0 END
) AS MatchingCondition, ...

The resulting number modulo N will yield how many rows match condition1. The number divided by N, modulo N will yield matches for condition2. The modulo remainder by N*N will yield matches for condition3, and so on:

num1 = result % N
result = (result - num1) / N
num2 = result % N
result = (result - num2) / N
num3 = result % N
...

(A further refinement using larger multipliers allows encoding in a single column the result of several non-mutually exclusive conditions).