SQL How to Update Sum of Column Over Group in Same Table

SQL How to Update SUM of column over group in same table

Assuming you are using SQL Server, I think you want something like this:

WITH toupdate AS
     (SELECT team, year, 
             Sum(personsales) OVER (partition BY team, year) AS newTeamSales 
      FROM salessummary
     ) 
UPDATE toupdate 
   SET teamsales = newteamsales; 

Your original query has several issues and suspicious constructs. First, an aggregation subquery is not updatable. Second, you are doing an aggregation and using a window function with, although allowed, is unusual. Third, you are aggregating by PersonSales and taking the sum(). Once again, allowed, but unusual.

MySql UPDATE with SUM in same table

UPDATE 
        results AS r 
    JOIN
        ( SELECT   user_id, 
                   SUM(score) AS sum_score
          FROM     results 
          WHERE    record_type = 'answer'
          GROUP BY user_id
        ) AS grp
       ON  
           grp.user_id = r.user_id 
SET 
       r.total = grp.sum_score
WHERE 
       r.record_type = 'email';

Regarding efficiency, an index on (record_type, user_id, score) would help both to efficiently compute the derived table and with the self-join.

MYSQL Update using SUM the same table

SUM() is used to sum an expression across multiple rows, usually using GROUP BY. If you want to add expressions in the same row you just use ordinary addition.

Use COALESCE() to provide a default value for null columns.

UPDATE total_tbl
SET total = COALESCE(month1, 0) + COALESCE(month2, 0) + COALESCE(month3, 0)

How to UPDATE table with SUM() and COUNT() in same table to different columns

try it use update value from left join (sum + count) table

update T T1
  left join (
    select `user`,sum(`sales`) newtotal,count(`order`) neworders
    from T
    group by `user`
  ) T2 on T1.`user` = T2.`user`
set T1.total = T2.newtotal,T1.orders = T2.neworders

---

Test DDL:

CREATE TABLE T
    (`user` varchar(4), `sales` int, `order` varchar(7), `total` int, `orders` int)
;

INSERT INTO T
    (`user`, `sales`, `order`, `total`, `orders`)
VALUES
    ('xx01', 100, 'order01', 0, 0),
    ('xx02', 200, 'order02', 0, 0),
    ('xx02', 400, 'order03', 0, 0),
    ('xx03', 300, 'order04', 0, 0),
    ('xx03', 500, 'order05', 0, 0)
;

---

Result:

| user | sales |   order | total | orders |
|------|-------|---------|-------|--------|
| xx01 |   100 | order01 |   100 |      1 |
| xx02 |   200 | order02 |   600 |      2 |
| xx02 |   400 | order03 |   600 |      2 |
| xx03 |   300 | order04 |   800 |      2 |
| xx03 |   500 | order05 |   800 |      2 |

---

TEST DEMO LINK

Updating two columns in the same table, using group by?

Use aggregation to bring them together:

UPDATE p
    SET Total_HR = b.Total_HR,
        High_BA =  b.High_BA
    FROM Spring_2021_BaseBall.dbo.People p JOIN
         (SELECT SUM(HR) as SUM_hr, MAX(BA) as High_BA
          FROM Spring_2021_BaseBall.dbo.Batting
          GROUP BY playerid 
         ) b
         ON b.playerid = p.playerid ;

How to update value of a column based on sum of same column values of other records in MYSQL?

Mysql multi table update manual page - https://dev.mysql.com/doc/refman/8.0/en/update.html

eg

drop table if exists t;

CREATE TABLE T(ID INT,SUBJECT VARCHAR(10), MARK INT);

INSERT INTO T VALUES
(1,'ALL',NULL),(1,'AAA',1),(1,'BBB',2),
(2,'ALL',NULL),(2,'AAA',10),(2,'BBB',20);

UPDATE T 
JOIN
 (SELECT ID,SUM(MARK) MARK FROM T WHERE SUBJECT <> 'ALL' GROUP BY ID) S ON S.ID = T.ID
SET T.MARK = S.MARK
WHERE SUBJECT = 'ALL';

---

Related Topics