How can I aggregate 'QuantityAvailable' for two rows with identical BranchCode and PartNumber in SQL?

Data and Databases
1

I have the following dataset and I’m looking to retain the ‘QuantityAvailable’ for other entries while combining the values for those that correspond to ‘0900-HSI’ and ‘0100-BLA’. Although the complete list of part numbers contains thousands of entries, I’ll showcase just one part number here for reference.

Here’s the dataset:

BranchID BranchCode PartNumber SupplierCode QuantityAvailable
1 0900-HSI GP6504-12-12 19015 5
2 0100-BLA GP6504-12-12 19015 1
3 0300-IGH GP6504-12-12 19015 2
4 0200-SCA GP6504-12-12 19015 0
5 0700-CLR GP6504-12-12 19015 0

I would like the result to appear as follows:

BranchID BranchCode PartNumber SupplierCode QuantityAvailable
1 0900-HSI GP6504-12-12 19015 6
2 0100-BLA GP6504-12-12 19015 6
3 0300-IGH GP6504-12-12 19015 2
4 0200-SCA GP6504-12-12 19015 0
5 0700-CLR GP6504-12-12 19015 0

Alternatively, I prefer this output:

BranchID BranchCode PartNumber SupplierCode QuantityAvailable
2 0100-BLA GP6504-12-12 19015 6
3 0300-IGH GP6504-12-12 19015 2
4 0200-SCA GP6504-12-12 19015 0
5 0700-CLR GP6504-12-12 19015 0

Here’s my current SQL query that fails to sum the values correctly; it only assigns a value of ‘1’ to ‘QuantityAvailable’:

SELECT 
  [BranchID], 
  [BranchCode], 
  [PartNumber], 
  [SupplierCode], 
  SUM(CASE 
    WHEN BranchCode IN ('0100-BLA', '0900-HSI') 
    THEN 1 
    ELSE QuantityAvailable 
  END) AS QuantityAvailable 
FROM [mydatabase].[dbo].[mytable] 
WHERE PartType IN ('Part', 'Exchange') 
  AND Inactive = 0 
  AND partnumber = 'GP6504-12-12' 
GROUP BY BranchID, BranchCode, PartNumber, SupplierCode;
2

Have you considered using a GROUP BY after calculating individual totals in a tmporary table or view? It might simplify things by separating concerns – compute totals first, then merge back to original. How will SQL Server’s optimization capabilities affect such a strategy, I wonder? :thinking:

3

Hmm, intriguing problem! Have you tried using a different approach with the SUM function? Maybe, instead of using CASE, you could play with adding a subquery to get the sum for both BranchCodes and then join it back? Can anyone think of any potential caveats with that approach?

4

You might try adjusting your CASE statement to add correct quantities. It seems you’re assigning 1 instead of actual QuantityAvailable. Use SUM(QuantityAvailable) in the subquery for specific BranchCodes and combine results. Test results step by step to make sure your sums are accurate.

5

One way to address this issue is to utilize a common table expression (CTE) to first compute the aggregated ‘QuantityAvailable’ for the specific BranchCodes you are interested in (‘0900-HSI’ and ‘0100-BLA’), and then use the CTE in your main query. This approach avoids confusion in conditional aggregation. After obtaining the sum, join it back to your main dataset to update only those two specific entries, while leaving all other entries as they are. This should help in achieving both alternative outputs you desire.

6

Another method could be using window functions in SQL to partition by PartNumber and sum the QuantityAvailable based on the BranchCode. This way, you can retain the individual row entries while updating just the necessary quantities for specific branches. Hope this gives you an added option! :blush: