CODEWITHSUNDEEP
mysqlsqlaggregate-functions
TDSii
TDSii

Reputation: 1725

mysql query grouping

I am making a shopping cart. Selected items are stored inside a database based on a search query.

How I can add the quantity for rows in case sku, type and color are the same values?

example:

sku  type   color  quantity
---------------------------
1    type1  blue   5
1    type1  blue   2
2    type1  blue   5
1    type1  green  5

my new rows should be:

sku  type   color  quantity
---------------------------
1    type1  blue   7
2    type1  blue   5
1    type1  green  5

Notice the first row quantity is now 7.

How can this be done? I tried GROUP BY, but didn't know how to make it dynamic to match all.

Upvotes: 0

Views: 63

Answers (3)

Alexey Sviridov
Alexey Sviridov

Reputation: 3510

CREATE TEMPORARY TABLE IF NOT EXISTS tmpTable LIKE dataTable

DELETE FROM tmpTable

Insert into tmpTable
select sku,type,color,sum(quantity) from dataTable
group by sku,type,color

DELETE FROM dataTable

INSERT INTO dataTable
SELECT * FROM tmpTable

Upvotes: 0

AbiusX
AbiusX

Reputation: 2404

  SELECT sku,type,color, 
         SUM(quantity) 
    FROM table 
GROUP BY sku,type,color

Upvotes: 2

Daniel Williams
Daniel Williams

Reputation: 9314

SELECT sku, type, color, sum(quantity)
FROM theTable
GROUP BY  sku, type, color

That will do it.

Upvotes: 1

Related Questions