sql - Can I match two tables in postgresql, sum the data in the number column and create a new column?

Question

I have two tables. I have about 6500 rows in one table and about 450 rows in other table. The data in the two tables are the same.

What I really want to do is to collect the data in the column "adet" in my table of 450 rows(the table name is "depoyaeklenecek") and the "adet" column in the 6500-row table(the table name is depoadetleri"). Thus, I would like to refresh my table in a way that I can write the number that I have collected in the "adet" column in the 6500-row table.

However, there is a problem, I think I have to match the ids in the "kapino" column to be able to add them. Because the data in both tables are not sorted in the same way, the capino data are in mixed order. I want it to collect the data in the "adet" column in two tables when the "kapino" ids in both tables are equal.

They are not tables with the same rows anyway. One is about 450 lines and the other is 6500 lines. Since the data in one of them will not be in the other, I need to check if the data in the kapino column is equal, then I need to collect it, I think I'm not sure.

Can I ask for help for this? Thanks

question from:https://stackoverflow.com/questions/65907504/can-i-match-two-tables-in-postgresql-sum-the-data-in-the-number-column-and-crea

Answer 1

I think what you want to do is a JOIN:

SELECT d1.kapino AS kapino, d1.adet + d2.adet AS adet_new
FROM depoyaeklenecek d1
INNER JOIN depoadetleri d2 ON d1.kapino = d2.kapino

Based on that you can do all kinds of other stuff, such as summing the values of adet etc.

Based on the comments:

SELECT kapino, small_counts.count - big_counts.count AS required
FROM (
  SELECT kapino, COUNT(1) count
  FROM depoyaeklenecek
  GROUP BY kapino
) small_counts
INNER JOIN (
  SELECT kapino, COUNT(1) count
  FROM depoadetleri
  GROUP BY kapino
) big_counts ON small_counts.kapino = big_counts.kapino