mysql query grouping

Question

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.

OMG Ponies · Accepted Answer · 2011-03-16 04:09:35Z

2

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

edited Mar 16, 2011 at 4:09

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

answered Mar 16, 2011 at 4:08

AbiusX

2,39521 silver badges27 bronze badges

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

5 Comments

TDSii Over a year ago

how i can update the tables after ward with those values? and remove the other duplicated now..

AbiusX Over a year ago

INSERT INTO table2 (select_statement_above); DROP table1;

AbiusX Over a year ago

You're either using a wrong ER or a bad code if this happens and you need to fix it.

TDSii Over a year ago

dropping the table is not a solution, any good cleanup process?

AbiusX Over a year ago

insert those to the same table, delete any rows below inserted_id

Daniel Williams · Accepted Answer · 2011-03-16 04:09:05Z

1

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

That will do it.

answered Mar 16, 2011 at 4:09

Daniel Williams

9,48016 gold badges80 silver badges119 bronze badges

4 Comments

TDSii Over a year ago

how i can update the tables afterward with those values? and remove the others.. remember i want to clean the table

Daniel Williams Over a year ago

Why do you want to alter the table? Just use these results.

TDSii Over a year ago

the purpose is to clean up my table

Daniel Williams Over a year ago

if the table is not being updated, then run this query, and put the results into a new, second table. Then empty out the old table and replace it with the new table's results. This is not great, since you're changing the meaning of the table, and not something to do regularly. but if it's a one-time cleanup, go for it.

Alexey Sviridov · Accepted Answer · 2011-03-16 07:46:50Z

0

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

edited Mar 16, 2011 at 7:46

answered Mar 16, 2011 at 4:09

Alexey Sviridov

3,49031 silver badges34 bronze badges

3 Comments

TDSii Over a year ago

how i can update the tables afterward with those values? and remove the others.. remember i want to clean the table

Alexey Sviridov Over a year ago

can't completely understand your question. delete from table1; insert into table1 select sku,type,color,sum(quantity) from ... group by sku,type,color? Or you want to replace data in table right inplace?

TDSii Over a year ago

i have a messy table that i want to clean it up, where the result is exactly the same command u made.

Collectives™ on Stack Overflow

mysql query grouping

3 Answers 3

5 Comments

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related