Database Administrators Asked by immobiluser on February 19, 2021
Hy, i want reduce my table and updating himself (group and sum some columns, and delete rows)
Source table "table_test" :
+----+-----+-------+----------------+
| id | qty | user | isNeedGrouping |
+----+-----+-------+----------------+
| 1 | 2 | userA | 1 | <- row to group + user A
| 2 | 3 | userB | 0 |
| 3 | 5 | userA | 0 |
| 4 | 30 | userA | 1 | <- row to group + user A
| 5 | 8 | userA | 1 | <- row to group + user A
| 6 | 6 | userA | 0 |
+----+-----+-------+----------------+
Wanted table : (Obtained by)
DROP TABLE table_test_grouped;
SET @increment = 2;
CREATE TABLE table_test_grouped
SELECT id, SUM(qty) AS qty, user, isNeedGrouping
FROM table_test
GROUP BY user, IF(isNeedGrouping = 1, isNeedGrouping, @increment := @increment + 1);
SELECT * FROM table_test_grouped;
+----+------+-------+----------------+
| id | qty | user | isNeedGrouping |
+----+------+-------+----------------+
| 1 | 40 | userA | 1 | <- rows grouped + user A
| 3 | 5 | userA | 0 |
| 6 | 6 | userA | 0 |
| 2 | 3 | userB | 0 |
+----+------+-------+----------------+
Problem : i can use another (temporary) table, but i want update initial table, for :
The result must be a reduce of initial table, group by user, and qty summed.
And it’s a minimal exemple, and i don’t want full replace inital from table_test_grouped, beacause in my case, i have another colum (isNeedGrouping) for decide if y group or not.
For flagged rows "isNeedGrouping", i need grouping.
For this exemple, a way to do is sequentialy to :
CREATE TABLE table_test_grouped SELECT id, SUM(qty) AS qty, user, isNeedGrouping FROM table_test WHERE isNeedGrouping = 1 GROUP BY user ;
DELETE FROM table_test WHERE isNeedGrouping = 1 ;
INSERT INTO table_test SELECT * FROM table_test_grouped ;
Any suggestion for a simpler way?
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP