kstahl
asked on
SQL Query to combine grouped records
This should be easy, but I'm up against the clock and having an issue.
Here's a sample of my data in one SQL table.
PartNumber, PartRev, Bin, Location, Lot, Qty
P-720, B, PAL8202,SH,F100,7
P-720, B, PAL8202,SH,F100,2
P-820, D, PAL8201,SH,F102,8
P-820, D, PAL8201,SH,F102,10
P-820, D, PAL8201,SH,G100, 4
I need to group records where the PartNumber, Rev, Bin, Location, and Lot match and then total the qty. I need an update query to set the new total and remove the extra records.
The query should result in
P-720, B, PAL8202,SH,F100,9
P-820, D, PAL8201,SH,F102,18
P-820, D, PAL8201,SH,G100, 4
The extra records should be deleted from my table. Thank you!
Here's a sample of my data in one SQL table.
PartNumber, PartRev, Bin, Location, Lot, Qty
P-720, B, PAL8202,SH,F100,7
P-720, B, PAL8202,SH,F100,2
P-820, D, PAL8201,SH,F102,8
P-820, D, PAL8201,SH,F102,10
P-820, D, PAL8201,SH,G100, 4
I need to group records where the PartNumber, Rev, Bin, Location, and Lot match and then total the qty. I need an update query to set the new total and remove the extra records.
The query should result in
P-720, B, PAL8202,SH,F100,9
P-820, D, PAL8201,SH,F102,18
P-820, D, PAL8201,SH,G100, 4
The extra records should be deleted from my table. Thank you!
ASKER
>So... you want to delete the records in this table, and populate it with the summed records, all in one SQL?
Yes, I need to replace the duplicates with one record summed if possible.
Yes, I need to replace the duplicates with one record summed if possible.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
FROM YourTableName
GROUP BY PartNumber, Rev, Bin, Location, Lot
ORDER BY PartNumber, Rev, Bin, Location, Lot
>I need an update query to set the new total and remove the extra records.
So... you want to delete the records in this table, and populate it with the summed records, all in one SQL?