SQL Query need to add the "QuantityAvailable" for 2 rows together that have the same Branc-CodePudding

I have the Data below. I want to keep the QuantityAvailable for the other columns but add/sum the data that matches the 0900-HSI and 0100-BLA. There is a long list of Part numbers (1000's) in the table that you dont see here. All you see is 1 Part number below to show the code.

This is the table data

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 want the output to be like this or

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

Or like this, This is what i prefer.

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 is my query that i have tried but it doesnt SUM, it just put a "1" in the "QuantityAvailable" column, see below after my query

SELECT
[BranchID],
[BranchCode],
[PartNumber],
[SupplierCode],
sum(Case
When BranchCode = '0100-BLA' or BranchCode = '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

CodePudding user response：

What you want is to group BranchCode 0900-HSI and 0100-BLA as one.

You can use case expression to treat both of the BranchCode as one and then SUM() it with a window function

SELECT [BranchID],
       [BranchCode],
       [PartNumber],
       [SupplierCode],
       SUM(SUM(QuantityAvailable)) 
         over (partition by
                case when [BranchCode] in ('0900-HSI', '0100-BLA')
                     then '0900-HSI 0100-BLA' else [BranchCode] 
                     end)  as QuantityAvailable
FROM  [mydatabase].[dbo].[mytable]
Group by Branchid, BranchCode, PartNumber, SupplierCode
order by Branchid

The second format that you preferred is actually much simpler. Use a case expression to translate one BranchCode to the other and then perform the group by with sum on the outer query

SELECT BranchID = MAX(BranchID),
       BranchCode,
       PartNumber,
       SupplierCode,
       QuantityAvailable = SUM(QuantityAvailable)
FROM
(
    SELECT [BranchID],
           [BranchCode] = CASE WHEN BranchCode = '0900-HSI' 
                               THEN '0100-BLA' 
                               ELSE BranchCode END,
           [PartNumber],
           [SupplierCode],
           [QuantityAvailable]
    FROM   [mydatabase].[dbo].[mytable]
) AS T
Group by BranchCode, PartNumber, SupplierCode
Order by Branchid

CodePudding user response：

You miss something in CASE statement. Try this - 

    SELECT
    [BranchID],
    [BranchCode],
    [PartNumber],
    [SupplierCode],
    sum(Case BranchCode
    WHEN '0100-BLA' THEN 1
    WHEN '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