DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to calculate percent on base?

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE