SELECT
ftxn.[Region_Group],
ftxn.[GroupBranch],
SUM(ftxn.[QTY]) AS [VOL],
SUM(ftxn.[AMT]) AS [AMT]
FROM
(SELECT
txn.[CustomerID],
txn.[BUID],
txn.[BranchID],
txn.[SKUCode],
txn.[QTY],
brn.[Region_Group],
brn.[GroupBranch],
pm.[SKU_Price],
txn.[QTY] * pm.[SKU_Price] AS [AMT]
-- TXN TABLE
FROM
(SELECT [CustomerID],[BUID],[BranchID],[SKUCode],[QTY] FROM [Sales_SKU_CDS]
UNION
SELECT [CustomerID],[BUID],[BranchID],[SKUCode],[QTY] FROM [Sales_SKU_CDS]) txn
-- USE [BUID],[BranchID] GET [GroupBranch],[Region_Group]
LEFT JOIN [Branch_LT] brn
ON txn.[BUID] = brn.[BUID]
AND txn.[BranchID] = brn.[BranchID]
-- USE [SKUCode] GET [SKU_Price]
LEFT JOIN [Product_Master] pm
ON txn.[SKUCode] = pm.[SKUCode]
-- USE [CustomerID] GET [Request_No]
LEFT JOIN
(SELECT
sbl.[CustomerID],
sbl.[Member_Number],
crm.[Request_No]
FROM [Customer_SBL] sbl
LEFT JOIN [CustomerRequest_CRM] crm
ON sbl.[Member_Number] = crm.[Member_Number]) cus
ON txn.[CustomerID] = cus.[CustomerID]
-- FILTER
WHERE cus.[Request_No] IN ('AA1234','BB4421')
) ftxn
GROUP BY
ftxn.[Region_Group],
ftxn.[GroupBranch]
ORDER BY
SUM(ftxn.[AMT]) DESC