I have a table which is called persons. It looks like this:

id,name,surname,status
1,name1,Garcia,parent
2,name2,Garcia,child
3,name3,Garcia,child
4,name4,Garcia,child
5,name5,Miller,parent
6,name6,Miller,child
7,name7,Miller,child

I want to add "childrencount" column which shows children count. If person is a child the value of "childrencount" column must be zero. I mean,

id,name,surname,status,childrencount
1,name1,Garcia,parent,3
2,name2,Garcia,child,0
3,name3,Garcia,child,0
4,name4,Garcia,child,0
5,name5,Miller,parent,2
6,name6,Miller,child,0
7,name7,Miller,child,0

I've tried:

SELECT SUM(CASE WHEN status = 'child' THEN 1 ELSE 0 END) AS childrencount FROM persons GROUP BY surname;

How can I write this query?

CodePudding user response：

Try a case expression inside sum():

select *,
case
when status = 'child' then 0
else sum(case when status = 'child' then 1 else 0 end) over(partition by surname)
end as childrencount
from table_name;

Fiddle

CodePudding user response：

Use SUM() window function inside a CASE expression:

SELECT *, 
       CASE status
         WHEN 'child' THEN 0
         WHEN 'parent' THEN SUM((status = 'child')::int) OVER (PARTITION BY surname) 
       END AS childrencount 
FROM persons;