swetha vijayan swetha vijayan - 3 months ago 10
SQL Question

How can we Group BY a column in SQL with inner join?

I will attach a screenshot, please look at it:

CLIK HERE TO VIEW PICTURE

In this I need to get sum of the [LOCAL CURRENCY] according to each each Branch Name

SELECT
BR.BranchName [BRANCH NAME],
PDS.ProductName [CURRENCY],
SUM(FCBD.Quantity) [QUANTITY],
FCBD.BuyingRate [RATE],
SUM(CONVERT(DECIMAL(12, 3), (FCBD.Quantity * FCBD.BuyingRate))) [LOCAL CURRENCY],
0 [TOTAL]
FROM
ALX_FCBuy FCB
INNER JOIN
ALX_FCBuyDetails FCBD ON FCB.FCBuyID = FCBD.FCBuyID
INNER JOIN
ALX_Branches BR ON FCB.BranchID = BR.BranchID
INNER JOIN
ALX_Products PDS ON FCBD.ProductID = PDS.ProductID
GROUP BY
BR.BranchName, FCBD.ProductID, PDS.ProductName, FCBD.BuyingRate

Answer
SELECT 
BR.BranchName [BRANCH NAME],
PDS.ProductName [CURRENCY], 
SUM(FCBD.Quantity) [QUANTITY],
FCBD.BuyingRate [RATE],
SUM(CONVERT(DECIMAL(12, 3), (FCBD.Quantity * FCBD.BuyingRate))) [LOCAL CURRENCY], 
SUM(SUM(CONVERT(DECIMAL(12, 3), FCBD.Quantity * FCBD.BuyingRate))) OVER (PARTITION BY BR.BranchName)  [TOTAL]
 FROM 
ALX_FCBuy FCB
 INNER JOIN 
ALX_FCBuyDetails  FCBD ON FCB.FCBuyID = FCBD.FCBuyID
 INNER JOIN 
ALX_Branches BR ON FCB.BranchID = BR.BranchID                   
 INNER JOIN 
ALX_Products PDS ON FCBD.ProductID = PDS.ProductID
 GROUP BY 
BR.BranchName, FCBD.ProductID, PDS.ProductName, FCBD.BuyingRate
Comments