By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 1,029 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

Combine rows, accumulate total

P: 6
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.
Dec 8 '06 #1
Share this Question
Share on Google+
8 Replies


P: 6
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 8 '06 #2

ronverdonk
Expert 2.5K+
P: 4,258
This what you want?
Expand|Select|Wrap|Line Numbers
  1.  select field1,field2, sum(quantity) from xx group by field1, field2;
Ronald :cool:
Dec 8 '06 #3

P: 6
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.
Dec 8 '06 #4

P: 6
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)
Dec 8 '06 #5

ronverdonk
Expert 2.5K+
P: 4,258
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.
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO xx (field1,field2,quantity) 
  2.    SELECT field1,field2, sum(quantity) AS quantity 
  3.    FROM  xx 
  4.    GROUP BY field1, field2;
Ronald :cool:
Dec 8 '06 #6

P: 6
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.
Dec 8 '06 #7

ronverdonk
Expert 2.5K+
P: 4,258
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:
Dec 8 '06 #8

P: 6
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).
Dec 8 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.