Geek Answers Handbook

Select a unique number of referenced lines

High level:

I have checklists and checklists. I want to get an account of the checklists that have been completed. In particular, checklists that contain checklist items, but they are all complete.

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 request will give me completed checklists? In particular, try to exclude checklists that have checklist items that are full and incomplete and a checklist that don't have 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'

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

+4
source share
5 answers

Faster, but:

 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;

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

+4
source

In this case, a subquery is used:

 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 a subquery to find out how many incomplete elements for each checklist.

+2
source

Try:

 SELECT COUNT(*) FROM ( SELECT 1 FROM checklist_items GROUP BY checklist_id HAVING bool_and(completed) ) as sq
+1
source

Assuming empty checklists are considered complete (although you can change this to remove empty checklists).

 SELECT checklists.id FROM checklists LEFT JOIN (SELECT DISTINCT checklist_id FROM checklist_items WHERE completed = 'f') partial_checklists ON checklists.id = partial_checklists.checklist_id WHERE partial_checklists.checklist_id = NULL

To exclude empty checklists (although I'm not sure about the syntax here):

 SELECT checklists.id FROM checklists LEFT JOIN (SELECT checklist_id FROM checklist_items WHERE completed = 'f' UNION DISTINCT SELECT checklist_id FROM checklist_items GROUP BY checklist_id HAVING count(*) = 0) partial_checklists ON checklists.id = partial_checklists.checklist_id WHERE partial_checklists.checklist_id = NULL
0
source 
 select count(*) completed_items from ( select c.id, c.name, count(*) total_items from checklists c inner join checklist_items ci on c.id = ci.checklist_id group by c.id, c.name having count(!completed or null) = 0 ) s
0
source

More articles:


All Articles