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

sql sql-server sql-server-2008

82 просмотра

2 ответа

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
Автор: Joji Thomas Eapen Источник Размещён: 08.11.2019 11:22

Ответы (2)


1 плюс

Решение
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
Автор: user6426615 Размещён: 21.08.2016 06:40

1 плюс

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
. . .
Автор: Gordon Linoff Размещён: 20.08.2016 01:57
Вопросы из категории :
32x32