Completes and totals, or how to count
In every third project I see SQL like this:
SELECT (SELECT COUNT(*) FROM table WHERE group_id = X AND completed = 1) as completed, (SELECT COUNT(*) FROM table WHERE group_id = X) as total FROM table WHERE group_id = X GROUP BY group_id
As you might understand the intention is to count all records in the group and those with
completed flag set. This solution gives me creeps.
Here’s how I would write the same:
SELECT COUNT(NULLIF(completed, 0)) as completed, COUNT(*) as total FROM table WHERE group_id = X
Now pick your DB book and go read what
NULLIF function is, and do me a favor. No more crazy subselects, ok?