Multiply two Column and Find the Percentage

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiply two Column and Find the Percentage

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-09-22 : 01:37:15

Friends,

I have the table called sales, find the table structure and sample data below

InvoiceId | Activity | UnitPrice | Quantity
1001 | AT1 | 23.454 | 4
1001 | AT2 | 12.4543 | 5
1002 | AT1 | 33.45 | 7

Now I want to calculate the Total and Percentage for Invoice ID 1001, So i will use the Invoice Id in Where Condition

Please find my expected OutPut
Activity | TotalPrice | Percentage
AT1 | 93.816 | 60.10
AT2 | 62.2715 | 39.98

Here TotalPrice = (UnitPrice * Quantity )
Percentage = TotalPrice / Sum(TotalPrice) * 100

Please help me to get the expected output

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-22 : 02:37:47

Might be this -


SELECT Activity, A.TotalPrice, A.TotalPrice/SumPrice*100 FROM 
(
SELECT InvoiceID, Activity, (UnitPrice*Quantity) AS TotalPrice  FROM YourTable
) A 
INNER JOIN 
(
SELECT InvoiceID, SUM(UnitPrice*Quantity) AS SumPrice FROM YourTable GROUP BY InvoiceID
) B
ON A.InvoiceID = B.InvoiceID
WHERE A.InvoiceID = 1001

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 02:38:55

SELECT S.Activity,A.TotalSum,(S.UnitPrice * S.quantity) AS [TotalPrice], ((S.UnitPrice * S.quantity)*100)/ A.TotalSum AS [Percentage]
FROM
(
SELECT InvoiceId,SUM(UnitPrice*quantity) AS [TotalSum] FROM Sales
WHERE InvoiceId =1001
GROUP BY InvoiceId
)A
INNER JOIN Sales S
ON A.InvoiceId = S.InvoiceId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources