Update Count column in Postgresql

Question

I have a single table laid out as such:

id  |  name  |  count
1   |  John  |
2   |  Jim   |
3   |  John  |
4   |  Tim   |

I need to fill out the count column such that the result is the number of times the specific name shows up in the column name.

The result should be:

id  |  name  |  count
1   |  John  |  2
2   |  Jim   |  1
3   |  John  |  2
4   |  Tim   |  1

I can get the count of occurrences of unique names easily using:

SELECT COUNT(name)
FROM table
GROUP BY name

But that doesn't fit into an UPDATE statement due to it returning multiple rows.

I can also get it narrowed down to a single row by doing this:

SELECT COUNT(name)
FROM table
WHERE name = 'John'
GROUP BY name

But that doesn't allow me to fill out the entire column, just the 'John' rows.

jpw · Accepted Answer · 2015-09-02 19:34:01Z

13

you can do that with a common table expression:

with counted as (
   select name, count(*) as name_count
   from the_table
   group by name
) 
update the_table
  set "count" = c.name_count
from counted c
where c.name = the_table.name;

Another (slower) option would be to use a co-related sub-query:

update the_table
  set "count" = (select count(*) 
                 from the_table t2 
                 where t2.name = the_table.name);

But in general it is a bad idea to store values that can easily be calculated on the fly:

select id,
       name, 
       count(*) over (partition by name) as name_count
from the_table;

edited Sep 2, 2015 at 19:34

jpw

45k6 gold badges71 silver badges90 bronze badges

answered Sep 2, 2015 at 19:28

user330315

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

1 Comment

Paul Maxwell Over a year ago

do take note of in general it is a bad idea to store values that can easily be calculated (kudos a_horse_with_no_name)

Vivek S. · Accepted Answer · 2015-09-03 04:46:55Z

2

Another method : Using a derived table

UPDATE tb
SET count = t.count
FROM (
    SELECT count(NAME)
        ,NAME
    FROM tb
    GROUP BY 2
    ) t
WHERE t.NAME = tb.NAME

edited Sep 3, 2015 at 4:46

answered Sep 3, 2015 at 4:41

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

Update Count column in Postgresql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related