How to get sum of values per id and update existing records in other table

Question

I have two tables like:

ID | TRAFFIC fd56756 | 4398 645effa | 567899 894fac6 | 611900 894fac6 | 567899

and

USER | ID | TRAFFIC andrew | fd56756 | 0 peter | 645effa | 0 john | 894fac6 | 0

I need to get SUM ("TRAFFIC") from first table AND set column traffic to the second table where first table ID = second table ID. ID's from first table are not unique, and can be duplicated.
How can I do this?

Use code tags next time, no need to insert HTML spaces. Code tags are where you write your question, in the toolbar at the left and are like this {}. — gsamaras
– gsamaras, Commented May 19, 2014 at 14:22
There is no column traffic in your second table. Please clarify. Also, columns and values in the second table seem to be switched. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 19, 2014 at 21:09

Erwin Brandstetter · Accepted Answer · 2014-05-20 18:28:50Z

Table names from your later comment. Chances are, you are reporting table and column names incorrectly.

UPDATE users u SET "TRAFFIC" = sub.sum_traffic FROM ( SELECT "ID", sum("TRAFFIC") AS sum_traffic FROM stats.traffic GROUP BY 1 ) sub WHERE u."ID" = sub."ID";

Aside: It's unwise to use mixed-case identifiers in Postgres. Use legal, lower-case identifiers, which do not need to be double-quoted, to make your life easier. Start by reading the manual here.

d1ll1nger · Accepted Answer · 2014-05-19 20:56:20Z

0

Something like this?

UPDATE users t2 SET t2.traffic = t1.sum_traffic FROM (SELECT sum(t1.traffic) t1.sum_traffic FROM stats.traffic t1) WHERE t1.id = t2.id;

edited May 19, 2014 at 20:56

answered May 19, 2014 at 14:23

d1ll1nger

1,70113 silver badges16 bronze badges

2 Comments

user3652819 Over a year ago

I have first table called stats.traffic with column "ID" AND "TRAFFIC" (uppercase) and second called users with "USER" , "ID" and "TRAFFIC" (uppercase). So what mean t2 and t1 ? This something like temp var ? I'm trying many times but unfortunately cannot execute query (I'm newbie, sorry) How query will looks like with my "uppercase" column names?

d1ll1nger Over a year ago

Modified my example to suit your case then. The t1 and t2 are aliases for the table name, just to save typing table1.xxx.

Collectives™ on Stack Overflow

How to get sum of values per id and update existing records in other table

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related