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!
