TransWikia.com

MySQL Transform multiple rows into a single row in same table (reduce by merge group by)

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 :

  • 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?

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP