I have following tables: Parent:

Child:

I have a following select statement:

select count(distinct parent.id)                                             as all,
       count(distinct parent.id) filter (where parent.status = 'CREATED')    as new,
       count(distinct parent.id) filter (where parent.status = 'SIGN')       as sign,
       count(distinct parent.id) filter (where parent.status = 'SENT')       as send,
       
       --**what i want to add is**:
       count(distinct parent.id *if any child element has status 'a', if two child elements' status is 'a' then parent must be considered as  1*)  as with_child_element 
from "parent";

As you can see I have aliases as all|new|sign|send, and I want to add a new one (with_child_element) which will count parent records of which its any child element has status "1"

How to get the expected result? Any approach for solving this problem is considered

no idea how to group child elements without group by statement and inside an aggregate function (count)

CodePudding user response：

You can use correlated EXISTS in the filter

select count(distinct parent.id)                                             as all,
       count(distinct parent.id) filter (where parent.status = 'CREATED')    as new,
       count(distinct parent.id) filter (where parent.status = 'SIGN')       as sign,
       count(distinct parent.id) filter (where parent.status = 'SENT')       as send,
       count(distinct parent.id) filter (where exists( 
               select 1 
               from Child c 
               where c.parent_id = parent.id and c.status ='a'
             )) as status_a
from "parent";

CodePudding user response：

You can do it as follows using left join to child table :

select p.id,
       count(distinct p.id)                                        as all,
       count(distinct p.id) filter (where p.status = 'CREATED')    as new,
       count(distinct p.id) filter (where p.status = 'SIGN')       as sign,
       count(distinct p.id) filter (where p.status = 'SENT')       as send,
       count(distinct c.id) filter (where c.status = 'a')          as with_child_element 
from parent p
left join child c on c.parent_id = p.id
group by p.id;

Demo here