T-SQL Tutorial

How to find percentage


How to find percentage in SQL Server?

To find the percentage of a column in SQL Server, you can use the SQL Aggregate functions and T-SQL Analytic functions.


Examples

First, we will create the ebook_sales table and insert records into it. Based on this table, we will show several examples using SQL aggregate functions and T-SQL analytical functions.

CREATE TABLE ebook_sales(id int, name varchar(250), price int);
INSERT INTO ebook_sales(id, name, price) values (1, 'SQL', 10);
INSERT INTO ebook_sales(id, name, price) values (2, 'T-SQL', 30);
INSERT INTO ebook_sales(id, name, price) values (3, 'SQL', 10);
INSERT INTO ebook_sales(id, name, price) values (4, 'MySQL', 20);
INSERT INTO ebook_sales(id, name, price) values (5, 'HTML', 10);
INSERT INTO ebook_sales(id, name, price) values (6, 'SQL', 10);
INSERT INTO ebook_sales(id, name, price) values (7, 'T-SQL', 30);
INSERT INTO ebook_sales(id, name, price) values (8, 'MySQL', 20);
INSERT INTO ebook_sales(id, name, price) values (9, 'PHP', 25);
INSERT INTO ebook_sales(id, name, price) values (10, 'SQL', 10);


Use COUNT and ROUND to get percentage

The ROUND function function will take a numeric value and round it to the specified number of decimal places. To get a percentage, you will need to multiply the rounded value by 100.

SELECT price,
ROUND(100.0 * price_sum / (SELECT COUNT(*) FROM ebook_sales), 2) AS price_pct
FROM (SELECT price, COUNT(*) AS price_sum
FROM ebook_sales
GROUP BY price
) x ORDER BY price;

Result

priceprice_pct
1050.000000000000
2020.000000000000
2510.000000000000
3020.000000000000

Use OVER clause to get percentage

The SQL Server OVER clause is used to specify a set of rows over which an aggregation operation will be performed or before the associated window function is applied.

SELECT name,
100 * COUNT(*) / SUM(COUNT(*)) OVER() percent_result
FROM ebook_sales
GROUP BY name;

Result

namepercent_result
HTML10
MySQL20
PHP10
SQL40
T-SQL20





Using PERCENT_RANK function

You can also use the T-SQL PERCENT_RANK function to find percentages in SQL Server. The PERCENT_RANK function will return a percentage rank for a given value within a set of values. For example, the following query will return the percentage rank of the price column:

SELECT DISTINCT s.price,
PERCENT_RANK() OVER (ORDER BY s.price ) AS PctRank
FROM ebook_sales s;

Result

PricePctRank
100
200,555555555555556
250,777777777777778
300,888888888888889