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?