CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Dev
Dev

Reputation: 1794

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

Upvotes: 0

Views: 239

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If you need the sum for each branch, then only put that in the SELECT and GROUP BY:

SELECT BR.BranchName [BRANCH NAME],
        SUM(CONVERT(DECIMAL(12, 3), FCBD.Quantity * FCBD.BuyingRate)) [LOCAL CURRENCY], 
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;

If you want an additional column in your existing result set, then the calculation would use window functions:

SELECT . . .
       SUM(SUM(CONVERT(DECIMAL(12, 3), FCBD.Quantity * FCBD.BuyingRate))) OVER (PARTITION BY BR.BranchName) as Total
. . .

Upvotes: 1

user6426615
user6426615

Reputation: 

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

Upvotes: 1

Related Questions