The following SQL Server T-SQL scripts illustrates the calculation of percent of orders by year and by country on base of all orders. The CONVERT function is used to format the percentage figures in currency format.
USE AdventureWorks2008;
GO
– Calculate percent sql – SQL Server calculate percentage – sql convert percent
SELECT YEAR=YEAR(OrderDate),
Percentage = convert(VARCHAR,convert(MONEY,100.0 * SUM(TotalDue) /
(SELECT SUM(TotalDue) FROM Sales.SalesOrderHeader)),1) + ‘%’
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY YEAR ASC
GO
/*
YEAR Percentage
2001 10.18%
2002 28.34%
2003 38.60%
2004 22.88%
*/
————
USE Northwind;
GO
– SQL calculate percentage – calculate percentage sql
SELECT Country,
Percentage = convert(VARCHAR,convert(MONEY,100.0 * count(* ) /
(SELECT count(* ) FROM Orders)), 1) + ‘%’
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY Country
ORDER BY count(* ) DESC
GO
/* Partial results
Country Percentage
USA 14.70%
Germany 14.70%
Brazil 10.00%
France 9.28%
UK 6.75%
Venezuela 5.54%
Austria 4.82%
*/
