Updating row count column in a table by counting number of rows in another table

Question

Table 1 (~1000 rows): Column_names : date, time, location_name, sum_severity_of_incident

Table 2 (~1000,000 rows) Column_names: date, time, location_name, vehicle_name, number of people, severity_of_incident - (double precision type - between 0 & 1)

Table 2 contains list of incidents with severity. I am trying to fill the values in the sum_severity_of_incident column in table 1 by summing the values in the severity_of_incident column in Table 2 which have the same date, time & location_name.

I am new to Postgresql and this problem looks like a FOR loop problem in any other programming language. I could not find a straightforward solution in postgresql.

Gordon Linoff · Accepted Answer · 2017-11-30 19:17:12Z

2

This is a simple update with group by:

update table1
    set num_of_incidents = t2.sums
    from (select date, time, location, sum(severity_of_incident) as sums
          from table2 
          group by date, time, location
         ) t2
    where t2.date = table1.date and t2.time = table1.time and t2.location = table1.location;

answered Nov 30, 2017 at 19:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Aman Over a year ago

Thanks. It worked perfectly. Just curious: Is this the fastest method? Can you think of any other way of doing this?

Gordon Linoff Over a year ago

@Aman . . . This should work well. Under some circumstances, a correlated subquery might have better performance.

Salim · Accepted Answer · 2017-11-30 19:18:53Z

0

Below SQL will give you number of incidents per date, time, location_name. Insert the result into table1.

select date, time, location_name, count(*) as number_of_incidents from table2 group by date, time, location_name;

answered Nov 30, 2017 at 19:18

Salim

2,18814 silver badges15 bronze badges

Collectives™ on Stack Overflow

Updating row count column in a table by counting number of rows in another table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related