Select unique count of rows referenced

Question

High level:

I have checklists and checklists have checklist items. I want get the count of checklists that been completed. Specifically, checklists that have checklist items but that are all completed.

Tables:

Table "checklists"

|     Column   |          Type          |
+--------------+------------------------+
| id           | integer                |
| name         | character varying(255) |

Table "checklist_items"

|     Column   |          Type          |
+--------------+------------------------+
| id           | integer                |
| completed    | boolean                |
| name         | character varying(255) |
| checklist_id | integer                |

Question: What query will give me the completed checklists count? Specifically, being careful to exclude checklists that have checklist items that are both complete and incomplete and checklist that have no checklist items.

Tried so far:

SELECT DISTINCT COUNT(DISTINCT "checklists"."id")
  FROM "checklists"
INNER JOIN "checklist_items" ON "checklist_items"."checklist_id" = "checklists"."id"
  WHERE "checklist_items"."completed" = 't'

Problem with this question is that it does not exclude partially completed checklists.

Answer 1

Faster, yet:

SELECT count(DISTINCT i.checklist_id)
FROM   checklist_items i
LEFT   JOIN checklist_items i1 ON i1.checklist_id = i2.checklist_id
                              AND i.completed IS NOT TRUE
WHERE i.completed
AND   i1.checklist_id IS NULL;

This only collects checklists where a completed item exists.
And excludes those where another checklist_item exists that is not completed (FALSE or NULL).

Answer 2

The following does this using a subquery:

select COUNT(*)
from (select cl.id,
             SUM(case when cli.completed <> 't' or cli.completed is null then 1 else 0 end) as NumIncomplete
      from checklists cl join
           checklist_items cli
           ON cli.checklist_id = cl.id
      group by cl.id
     ) t
where NumIncomplete = 0

You can run the subquery to see how many incomplete items there are for each check list.