Merge duplicate values in column

Question

I have a table cart_items with the following data

+-----+---------+---------+------------+----------+
| id  | user_id | cart_id | product_id | quantity |
+-----+---------+---------+------------+----------+
| 303 |       9 |      44 |          1 |        2 |
| 305 |       9 |      44 |          3 |        1 |
| 307 |       9 |      44 |          3 |        1 |
| 308 |       9 |      44 |          2 |        1 |
| 309 |       9 |      44 |          6 |        1 |
| 310 |       9 |      44 |          2 |        1 |
+-----+---------+---------+------------+----------+

My problem is that there are duplicate products. My desired table would be this

+-----+---------+---------+------------+----------+
| id  | user_id | cart_id | product_id | quantity |
+-----+---------+---------+------------+----------+
| 303 |       9 |      44 |          1 |        2 |
| 305 |       9 |      44 |          3 |        2 |
| 308 |       9 |      44 |          2 |        2 |
| 309 |       9 |      44 |          6 |        1 |
+-----+---------+---------+------------+----------+

So the difference is that the duplicates product_id got merged and increased the quantity.

Is there an easy way to do this with an SQL query?

forpas · Accepted Answer · 2019-07-25 22:13:00Z

1

You need to group by user_id, cart_id, product_id and aggregate:

select
  min(id) id, user_id, cart_id, product_id, sum(quantity) quantity
from cart_items
group by user_id, cart_id, product_id

answered Jul 25, 2019 at 22:13

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pedro Martins Over a year ago

Thank you, group by solved it. Never thought it would be a simple thing like that.

Collectives™ on Stack Overflow

Merge duplicate values in column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related