Thursday, May 24, 2007

Getting Percentage from SQL Queries

This is the way I got a Percentage Column in my Query. For this I used Microsoft Access's Northwind Database which is available along with Office Package.

Lets Assume that We need to show all Customer's Total Order Value from Northwind.mdb. For that I used the following Query:

SELECT
Customers.CompanyName,
Sum([UnitPrice]*[Quantity]) AS CustTotal,
FROM
(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Customers.CompanyName, Orders.CustomerID
ORDER BY Sum([UnitPrice]*[Quantity]) DESC;


I figured that to get the percentage i need to use the following simple formula:

Percentage = [Client Total]/[Overall Total] * 100;


So to get the percentage I added the following SQL which is a translation of the above formula:

(
((Sum([UnitPrice]*[Quantity])/(SELECT SUM([UnitPrice]*[Quantity]) FROM [Order Details]))*100)
) AS Percentage


So the Overall SQL looks something as after add:

SELECT
Customers.CompanyName,
Sum([UnitPrice]*[Quantity]) AS CustTotal,
(
((Sum([UnitPrice]*[Quantity])/(SELECT SUM( [UnitPrice]*[Quantity]) FROM [Order Details]))*100)
) AS Percentage
FROM
(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Customers.CompanyName, Orders.CustomerID
ORDER BY Sum([UnitPrice]*[Quantity]) DESC;


PLEASE! if you found this interesting, do drop me a comment!

No comments: