1

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 :

  • grouping by user and sum qty
  • replace/merge rows into only one by group

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.