Combine rows, accumulate total

[cycleops]

Can anyone please help?
I'm trying to combine 2 or more similar rows into one row, accumulating a field for quantity. E.g.:

index, field1, field2, quantity
1, ccc, bbb, 5
2, aaa, bbb, 15
3, aaa, bbb, 3

Because the contents of fields 1 & 2 are the same in rows 2 and 3, I want to end up with only one row with the total quantity.
index, field1, field2, quantity
1, ccc, bbb, 5
2, aaa, bbb, 18

It doesn't matter if the row is a new one with a new index. Eg.
index, field1, field2, quantity
1, ccc, bbb, 5
4, aaa, bbb, 18

Thanks in advance for any help.

[cycleops]

I suppose I should say that I'm not necessarily looking for a complete answer, any pointers would be good.
My approaches have been using

SELECT DISTINCT

which (obviously) is wrong, and the other bad approach was to select all possibly relevant rows and process them in PHP.
Again, any help is appreciated.

[ronverdonk]

This what you want?

 select field1,field2, sum(quantity) from xx group by field1, field2;

Ronald :cool:

[cycleops]

I think that's a piece of it -- it produces a correct record. What would you do next, insert it and delete the 2 rows? How is that done as one query?

Thanks so much Ronverdonk. I'm going to try expanding on your input.

[cycleops]

I'm still drawing a blank.
It's the approach I'm struggling with -- it's so different from a procedural language.
How do you combine such diverse operations into one query?
I seem to have a SELECT, to find the similar rows,
an UPDATE or INSERT, to write the total quantity record
and one or more DELETEs, to get rid of the spurious row(s)

[ronverdonk]

I can give you it up to the INSERT. But the Delete in the same statement? I don't think so, but maybe someone knows better.

INSERT INTO xx (field1,field2,quantity) 
   SELECT field1,field2, sum(quantity) AS quantity 
   FROM  xx 
   GROUP BY field1, field2;

Ronald :cool:

[cycleops]

Wow, I stumped the Community Expert!
As an SQL novice, I assumed this part would be straightforward . I'll have to revisit the rest of my application and find a way to avoid the situation arising.
Thanks for the input, Ronald.

[ronverdonk]

I am not so easily stumped!

Are you talking about all functions in 1 query or all functions in 1 statement?
You can of course have more then 1 statement in a string and send that as 1 query in your programming language to the db.

Ronald :cool:

[cycleops]

I'm not sure what the difference is. Don't think that it matters except for the end result (similar rows are combined to show total, the row with total replaces the 2 or more rows that were in the database).