CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ayman
Ayman

Reputation: 872

Calculated column issue in sql query

I have an issue with this below query in calculated column Cost %

Cost % = Amount/SUM(Amount) * 100

SUM(Amount) equals 2599.30 from the data that I have provided.

SELECT
    S.Product_Name,
    SPD.UnitPrice,
    SPD.Quantity,
    SUM(SPD.Quantity * SPD.UnitPrice) AS Amount,
    SPD.Quantity * SPD.UnitPrice) / SUM(SPD.Quantity * SPD.UnitPrice) * 100 as [Cost %]
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
GROUP BY
    S.Product_Name,
    SPD.UnitPrice,
    SPD.Quantity

The output that I get from this query:

Product_Name    UnitPrice   Quantity        Amount  Cost %
pro1             141.55           3         424.65  100.0000000000000
pro2             151.05           3         453.15  100.0000000000000
pro3              40.38           4         161.52  100.0000000000000
pro4              24.08           6         144.48  100.0000000000000
pro5             707.75           2         1415.50 100.0000000000000

The output that I am looking for:

Product_Name    UnitPrice   Quantity        Amount      Cost %
pro1             141.55           3         424.65      16.33
pro2             151.05           3         453.15      17.43
pro3              40.38           4         161.52      6.21
pro4              24.08           6         144.48      5.55
pro5             707.75           2         1415.50     54.45

Upvotes: 0

Views: 51

Answers (1)

Lamak
Lamak

Reputation: 70638

First of all, you should start using table aliases to make your code more readable. Anyway, you should try:

SELECT  S.Product_Name, 
        SPD.UnitPrice, 
        SPD.Quantity, 
        SPD.Quantity * SPD.UnitPrice Amount,
        (SPD.Quantity * SPD.UnitPrice)/(SUM(SPD.Quantity * SPD.UnitPrice) OVER()) * 100 [Cost %]
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;

Upvotes: 2

Related Questions