Update column with the sum of another one

sql update sum from same table
update column with sum of other columns
sql update group by sum
sql-update sum from another table
sql sum multiple columns
sql sum group by multiple columns
update query with sum function in mysql
sql sum multiple rows

I used PgAdmin4 to develop a PostGIS database. I tried to update a column (called "surface_net") in a table called "parcelles" with the sum of another column (called "surface_cultures") in another table called "zones_cultures". The table "zones_cultures" has a foreign key with "parcelles" ("zones_cultures.id_parcelles" = "parcelles.id_egrid").

To sum up, the column "surface_net" is the sum of "zones_cultures" group by the "id_egrid".

So I did this SQL query to update the column "surface_net":

UPDATE public.parcelles
    SET surface_net=
    (SELECT sum(zones_cultures.surface_cultures)
        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles
        group by parcelles.id_egrid);

But it doesn't work. Here is the result:

ERROR:  more than one row returned by a subquery used as an expression
SQL state: 21000

Could someone help me with my SQL query to be able to update the column "surface_net"? Thanks in advance

You need a WHERE clause in the subquery that makes it return 1 relevant result for 1 row of the table you're updating. I believe this should work:

UPDATE public.parcelles p
    SET surface_net=
    (SELECT sum(zones_cultures.surface_cultures)
        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles
        where parcelles.id_egrid = p.id_egrid
        group by parcelles.id_egrid);

SQL update columns with sum() and group by, SQL update columns with sum() and group by Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 Here is a new document which is a collection of questions with short and simple  I used PgAdmin4 to develop a PostGIS database. I tried to update a column (called "surface_net") in a table called "parcelles" with the sum of another column (called "surface_cultures") in another table called "zones_cultures". The table "zones_cultures" has a foreign key with "parcelles" ("zones_cultures.id_parcelles" = "parcelles.id_egrid").

when you applied group by it returned multiple values as a result it thrown error, just remove group by it will work

UPDATE public.parcelles
    SET surface_net=
    (SELECT sum(zones_cultures.surface_cultures)
        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles
        );

Or if you want to update per id then use below no need group by

UPDATE public.parcelles p
    SET p.surface_net=
    (SELECT sum(zones_cultures.surface_cultures)
        FROM parcelles inner join zones_cultures on parcelles.id_egrid = zones_cultures.id_parcelles
        where parcelles.id_egrid = p.id_egrid
       );

SQL Server SUM() Function By Practical Examples, This tutorial shows you how to use SQL Server SUM() function to calculate the CREATE TABLE t( val INT ); INSERT INTO t(val) VALUES(1),(2),(3),(3),(4) The following statement returns the sum of all values in the val column: total ----------​- 18 Warning: Null value is eliminated by an aggregate or other SET operation. To use a formula to sum values in Column B based on Column A, you can create a formula based on the SUMIF function. Just do the following steps: #1 select the text values in Column A (A1:A6), press Ctrl +C to copy these values, and paste into another blank column (Column D).

You can try below

UPDATE public.parcelles p
    SET surface_net=sum(zones_cultures.surface_cultures)
 inner join zones_cultures where p.id_egrid = zones_cultures.id_parcelles

Documentation: 9.5: UPDATE, Any expression using the table's columns, and/or columns of other tables If it does, then only one of the join rows will be used to update the target row, but which UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) = (SELECT sum(x),  Please note that the range should contain both the column you will sum based on and the column you will sum. 2. In the opening Combine Rows Based on Column dialog box, you need to: (1) Select the column name that you will sum based on, and then click the Primary Key button; (2) Select the column name that you will sum, and then click the Calculate > Sum.

Using the Update Column Function in MapInfo Pro, When updating one table based on data from another table, specify Value, Min, Average, Sum, Max, Count, Proportion Sum, WtAvg, Proportion  Hello, I have this situation: TABLE2 Line Expense CodArea Date Office 1 10 Area1 01/01/2017 Office1 2 20 Area3 02/01/2017 Office1 3 15 Area2 03/02/2017 Office1 4 15 Area2 04/01/2017 Office1 5 20 Area3 05/01/2017 Office3 6 15 Area1 06/03/2017 Office2 7 30 Area3 07/01/2017 Office3 8 25 Area

How to get a formula field to total an entire table column, even if , Another problem is that it can only total a maximum of 85 cells. One workaround is to specify the row and column numbers in the formula; for example, to sum The “total” field will automatically update when you print, or go into Print Preview​  UPDATE table SET col = new_value WHERE col = old_value AND other_col = some_other_value; UPDATE table SET col = new_value WHERE col = old_value OR other_col = some_other_value; As you can see, you can expand the WHERE clause as much as you’d like in order to filter down the rows for updating to what you need.

Update value from SUM(values) in another table – Mike250, Update value from SUM(values) in another table I recently found myself forgetting the exact syntax to update a value in a table based on the sum of another set of values in One daughter. Field. Hara Noda 13 hours  In this post, we’ll learn how to add up a column of numbers based on the values in another column. For example, we are trying to analyze product sales based on average customer rating. That is, customers rate our products on a scale of 1 to 10, and so each product has an average rating such as 9.8, 7.2, 6.1, and so on.

Comments
  • Skip the GROUP BY
  • Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
  • I'm glad it did. Don't forget to upvote and accept if this worked for you.
  • Just a last question: do you know how I could create an "auto update" with this code? I want that every time I add new values in the "zones_cultures", the "parcelles" table is update automatically with the update you gave me.
  • @SophieCompagnon In general, you can do this in two ways: have parcelles be a view instead of a table, or by using a trigger. Using triggers should usually be avoided because they are a maintenance nightmare. So the best way to handle this is to create a view. If you need more help it would probably be best to create a new question for this.