SQL Combining multiple rows into a single row

[Jrod2541]

So I have a table named Table1. Inside Table1 are the fields: SKU, VendrItem, Qty and EachCost.

SKU   VendrItem   Qty   EachCost
123   ABC         2     $2      
123   ABC         5     $2      
475   TGE         4     $9      
887   ASW         8     $4      
475   TGE         45    $9       
788   WQQ         14    $1       
475   TGE         20    $9       
945   WWT         77    $3       
788   WQQ         8     $1      
 

I want to combine the rows with the same SKU number.

I want less rows without changing anything other than the Qty field.

So instead of having multiple SKU rows with different quantities, it should have a single SKU row with the combined quantities.

So I want the table to eventually look like this:

SKU   VendrItem   Qty   EachCost
123   ABC         7     $2            
475   TGE         69    $9      
887   ASW         8     $4            
788   WQQ         22    $1             
945   WWT         77    $3       
 

I don't know how (or even if) I can do this. What code in SQL do I write to combine rows?

[twinnyfo]

You can use a simple aggregate Query to Sum the Quantity field, but you would have to Append that to a new Table. I am not sure how to do that on your current table without being destructive or too ambiguous for hte query to figure out.

Is this an inventory list or a sales list? If it is an inventory list, how did the multiple entries get there, rather than updating the Quantities? If it is a sales list, then it would make sense to keep the separate records to show separate sales, and then only aggregate to provide a report of sales activity.

This is an unusual procedure, but there are plenty of circumstances that I can think of that would warrants combining of rows like this to get your data set right for future operations.

[Jrod2541]

What's happening here is these are the parts(SKU) customers order, I take these parts and choose the vendor(VendrItem)that sell that part the cheapest(EachCost).

I already have the cheapest vendor in there as well as the cheapest part, I need to combine the SKUs.

The reason the SKUs are seperated in the first place is because different customers ordered them, but they're still the same parts.

I chose the cheapest vendors that will sell me those parts and now I need to combine the rows so I can tell the vendor how much of each SKU I need.





In other words, let's say I have 3 customers: A, B and C. Customer A orders 60 parts (SKU # is 123), Customer B orders 10 parts(SKU # is 445) and Customer C orders 15 parts (SKU # is 123).

There are two vendors: NIU and RWE

NIU sells the SKU # 123 parts for $1 each and sells the SKU # 445 $5 each

The vendor RWE sells the SKU # 123 parts for $2 each and sells the SKU # 445 $4 each

I choose Vendor NIU for the SKU # 123 parts because they sell them cheaper and I choose vendor RWE for SKU # 445 because they're cheaper.

Vendors have different names for their items so for SKU # 123, the vendor NIU calls it 45-NY and for vendor RWE, SKU #445 is 432

So I make a query using the info above to spit out this table:

SKU    VendrItem    Qty   EachCost
123    45-NY        60    $1
123    45-NY        15    $1
445    432          10    $4
 

I want to give my order to the vendor, so Instead of showing them two different orders for a single part #, I want to combine them so I only give them one order instead of two.

SKU    VendrItem    Qty   EachCost
123    45-NY        75    $1
445    432          10    $4
 

Can you give me a link on where I can learn aggregate queries?

Is there another way I should do this?

[twinnyfo]

If this is your case, I would not alter your tables, but still use an aggregate query.

To use an aggregate query, in the query builder, there should be a Summation notation (capital Greek letter sigma). Click on that and this will add another row to your query builder called "Total". When you add fields to your query, the default "Total" row will be "Group By". But you can change this to Sum, Count, Where, Min, Max and others.

You will want to Group By "SKU", "Vendor Item" and "Cost" and Sum "Quantity".

Hope this helps. Aggregate queries are very useful when you get the hang of using them.

[Rabbit]

If that is your order detail table, shouldn't there be an order number of some sort? How will you know who bought what?

[NeoPa]

Combining Rows-Opposite of Union might lead you in a viable direction. Not necessarily best for what you need but I'll let you work that out for yourself.

[Jrod2541]

Thanks everyone. I used the method twinnyfo gave me and it worked.

[twinnyfo]

Jrod, glad I could be of service. Lots of different ways ot skin a cat.