Creating new column based on a condition in PostgreSQL

Question

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?

Zakaria · Accepted Answer · 2022-03-04 15:52:54Z

2

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

answered Mar 4, 2022 at 15:52

Zakaria

4,7762 gold badges8 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AlwaysJunior Over a year ago

thank you and your fiddle!

forpas · Accepted Answer · 2022-03-04 15:53:00Z

1

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;

answered Mar 4, 2022 at 15:53

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

Creating new column based on a condition in PostgreSQL

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related