Question 89911 in PostgreSQL Question posted in
The official documentation can be found here.

Counting parent records by status, and applying another count by considering that one of the child element must have a particular child-status – Postgresql

I have following tables:
Parent:

id status
1 SENT
2 CREATED

Child:

parent_id status
1 a
1 b

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)

Tags:

2

Answers


  1. 0

    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";
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search