|
The following Microsoft SQL Server T-SQL scripts illustrate the calculation of percent / percentage of on base total and on columns. The CONVERT function is used to format the percentage figures in percentage 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%
*/
------------
-- SQL convert rate to percent format - SQL percentage of two columns
SELECT TOP ( 3 )
ProductName = Name,
StandardCost / ListPrice AS CostToPriceRate,
CONVERT(DECIMAL(5,2),100.0 * StandardCost / ListPrice) AS [CostToPriceRatePct(%)],
CONVERT(VARCHAR,CONVERT(DECIMAL(5,1),100.0 *
StandardCost / ListPrice)) + '%' AS CostToPriceRatePct
FROM AdventureWorks2008.Production.Product
WHERE ListPrice > 0
ORDER BY NEWID()
/*
ProductName CostToPriceRate CostToPriceRatePct(%) CostToPriceRatePct
HL Mountain Frame-Black,42 0.5476 54.76 54.8%
LL Touring Seat/Saddle 0.444 44.40 44.4%
Short-Sleeve Classic Jersey, XL 0.77 77.00 77.0%
*/
------------
USE Northwind;
GO
-- SQL compute percentage of total - calculate percent / 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%
*/
------------
-- T-SQL applying OVER() to get summary total and percent on base
SELECT MONTH = MONTH(OrderDate),
SUM(TotalDue) AS SalesByMonth,
100.0 * ((SUM(TotalDue)) / (SUM(SUM(TotalDue))
OVER())) AS PctSalesByMonth
FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2003
GROUP BY MONTH(OrderDate)
ORDER BY MONTH
/*
MONTH SalesByMonth PctSalesByMonth
1 2233575.1127 4.11000
2 3705635.4979 6.82000
3 2611621.2596 4.80000
4 3041865.4414 5.60000
5 4449886.2315 8.19000
6 3257517.7011 5.99000
7 4681520.6399 8.62000
8 6775857.0745 12.47000
9 6762753.8141 12.45000
10 4243366.5942 7.81000
11 5961182.6761 10.97000
12 6582833.0438 12.12000
*/
------------
-- SQL percentage calculation based on the difference of two columns
SELECT TOP ( 7 ) ProductName = Name,
ListPrice,
[GrossMargin(%)] = convert(DECIMAL(5,1),
100.0 * (ListPrice-StandardCost) / ListPrice)
FROM AdventureWorks2008.Production.Product
WHERE ListPrice > 0
ORDER BY NEWID()
/* ProductName ListPrice GrossMargin(%)
ML Headset 102.29 55.6
Road-250 Black, 58 2443.35 36.4
Road-150 Red, 62 3578.27 39.3
Mountain-500 Silver, 40 564.99 45.4
HL Touring Handlebars 91.57 55.6
Full-Finger Gloves, S 37.99 58.7
LL Crankset 175.49 55.6 */
------------
-- SQL percent of total calculation - SQL percentage on base total
SELECT YEAR(OrderDate) AS [Year],
SUM(CASE DATEPART(QQ,OrderDate) WHEN 1 THEN TotalDue
ELSE 0 END) AS QTR1,
SUM(CASE DATEPART(QQ,OrderDate) WHEN 2 THEN TotalDue
ELSE 0 END) AS QTR2,
SUM(CASE DATEPART(QQ,OrderDate) WHEN 3 THEN TotalDue
ELSE 0 END) AS QTR3,
SUM(CASE DATEPART(QQ,OrderDate) WHEN 4 THEN TotalDue
ELSE 0 END) AS QTR4,
CONVERT(varchar,SUM(TotalDue),1) AS SalesTotal,
CONVERT(DECIMAL(5,1),(SUM(TotalDue) /
(SELECT SUM(TotalDue)
FROM AdventureWorks2008.Sales.SalesOrderHeader) * 100.0))
AS YearPercentOfTotal
FROM AdventureWorks2008.Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY YEAR
| Year |
QTR1 |
QTR2 |
QTR3 |
QTR4 |
SalesTotal |
YearPercentOfTotal |
| 2001 |
0 |
0 |
5850933 |
8476619 |
14,327,552.23 |
10.2 |
| 2002 |
7379686 |
8210285 |
13458206 |
10827327 |
39,875,505.10 |
28.3 |
| 2003 |
8550832 |
10749269 |
18220132 |
16787382 |
54,307,615.09 |
38.6 |
| 2004 |
14170983 |
17969751 |
56178.92 |
0 |
32,196,912.42 |
22.9 |
------------
-- SQL PERCENT ON TOTAL / PERCENT ON BASE calculation - Sales by Product Category
USE Northwind;
SELECT C.CategoryName,
SUM(ODE.ExtendedPrice) AS CategorySales,
SUM(ODE.ExtendedPrice) / (SELECT SUM(ExtendedPrice)
FROM [Order Details Extended]
) AS FractionOfTotal,
CONVERT(DECIMAL(5,1),100.0 *
SUM(ODE.ExtendedPrice) / (SELECT SUM(ExtendedPrice)
FROM [Order Details Extended]
)) AS PercentOnBase,
(SELECT SUM(ExtendedPrice)
FROM [Order Details Extended]) AS TOTAL
FROM Categories C
INNER JOIN (Products P
INNER JOIN (Orders O
INNER JOIN [Order Details Extended] ODE
ON O.OrderID = ODE.OrderID)
ON P.ProductID = ODE.ProductID)
ON C.CategoryID = P.CategoryID
GROUP BY C.CategoryName
ORDER BY C.CategoryName
GO
/*
CategoryName CategorySales FractionOfTotal PercentOnBase TOTAL
Beverages 257874.23 0.2115 21.2 1219012.58
Condiments 102325.24 0.0839 8.4 1219012.58
Confections 159389.18 0.1307 13.1 1219012.58
Dairy Products 226197.45 0.1855 18.6 1219012.58
Grains/Cereals 90654.10 0.0743 7.4 1219012.58
Meat/Poultry 160369.92 0.1315 13.2 1219012.58
Produce 94741.72 0.0777 7.8 1219012.58
Seafood 127460.74 0.1045 10.5 1219012.58
*/
------------
Related articles:
Computing Percentiles in SQL Server
How to calculate percentage with a SQL statement
Calculate Percentiles with SQL Server 2005
|
