This relates to counting the number of records that match a certain condition, e.g.
invoice amount > $100.
I tend to prefer
COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)
However, this is just as valid
SUM(CASE WHEN invoice_amount > 100 THEN 1 ELSE 0 END)
I would have thought COUNT is preferable for 2 reasons:
- Conveys the intention, which is to
COUNTprobably involves a simple
i += 1operation somewhere, whereas SUM cannot count on its expression to be a simple integer value.
Does anyone have specific facts about the difference on specific RDBMS?