SQL Server 2008 – Getting Sum of Calculated Columns

sql serversql-server-2008

How can I get the sum of TotalAmount as single value for example

Product Name UnitPrice  Quantity    Discount%      Amount     SharingCost%  ShippingCost    CustomsCost  TotalAmount
pro1        250.00      12          10.00           3000.00   75             375.00         0.00             3075.00
pro2        100.00      10          10.00           1000.00   25             125.00         125.00           1025.00

from the above sample data sum of TotalAmountis 4100.00.

How can I get sum value which is 4100.00 in single column for specific Purchase_ID

Note

Parent table is Stock_Purchase

Child table is Stock_Purchase_Details

Query

SELECT
  S.Product_Name,
  SPD.*,
  Sp.Delivery_Status,
  CONVERT(decimal(10, 2), (SPD.Quantity * SPD.UnitPrice)) Amount,
  CONVERT(int, (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * 100) [Cost%],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost AS decimal(10, 2)) AS [ShippingCost],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost AS decimal(10, 2)) AS [CustomsCost],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost + (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost + SPD.Quantity * SPD.UnitPrice - (SPD.Discount / 100) * SPD.Quantity * SPD.UnitPrice AS decimal(10, 2)) AS [TotalAmount]
FROM dbo.Stock_Purchase SP
INNER JOIN dbo.Stock_Purchase_Details SPD
  ON SP.Purchase_ID = SPD.Purchase_ID
INNER JOIN dbo.Store S
  ON SPD.Pro_ID = S.Pro_ID
WHERE SPD.Purchase_ID = '1'

Best Answer

Try naming that query, and then using the column with PARTITION BY in an OVER clause.

WITH MyCTE AS (
SELECT
  S.Product_Name,
    SPD.*,
    Sp.Delivery_Status,
  CONVERT(decimal(10, 2), (SPD.Quantity * SPD.UnitPrice)) Amount,
  CONVERT(int, (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * 100) [Cost%],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost AS decimal(10, 2)) AS [ShippingCost],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost AS decimal(10, 2)) AS [CustomsCost],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost + (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost + SPD.Quantity * SPD.UnitPrice - (SPD.Discount / 100) * SPD.Quantity * SPD.UnitPrice AS decimal(10, 2)) AS [TotalAmount]
FROM dbo.Stock_Purchase SP
INNER JOIN dbo.Stock_Purchase_Details SPD
  ON SP.Purchase_ID = SPD.Purchase_ID
INNER JOIN dbo.Store S
  ON SPD.Pro_ID = S.Pro_ID
)
SELECT *, SUM(TotalAmount) OVER (PARTITION BY Purchase_ID)
FROM MyCTE;

But also, you may like to consider using APPLY to make this a lot simpler. Read http://blogs.lobsterpot.com.au/2011/12/13/a-t-sql-tip-working-calculations/ for more info on this.

Related Question