CODEWITHSUNDEEP
sql
Pedro Martins
Pedro Martins

Reputation: 168

Merge duplicate values in column

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?

Upvotes: 0

Views: 42

Answers (1)

forpas
forpas

Reputation: 164069

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

Upvotes: 1

Related Questions