Calculate Percentage in SQL Server

The following T-SQL script shows a simple example to calculate Percentage in SQL Server. We will be using a table which contains Grades and calculate the percentage weight of each grade, for the entire classroom

SAMPLE DATA

CREATE TABLE #ClassRoom(ID INT IDENTITY(1,1), Grade char(2) NULL);
GO
-- Code by SqlServerCurry.com
INSERT INTO #ClassRoom Values ('A');
INSERT INTO #ClassRoom Values ('B');
INSERT INTO #ClassRoom Values ('B+');
INSERT INTO #ClassRoom Values ('B');
INSERT INTO #ClassRoom Values ('A');
INSERT INTO #ClassRoom Values ('A+');
INSERT INTO #ClassRoom Values ('B');
INSERT INTO #ClassRoom Values ('B');
INSERT INTO #ClassRoom Values ('A+');
INSERT INTO #ClassRoom Values ('C');

QUERY

SELECT Grade,
CONVERT(varchar, 100 * count(*) / tot,1) + '%' as 'Percent'
FROM #ClassRoom,
(SELECT COUNT(*) as tot FROM #ClassRoom) x
GROUP BY Grade, tot

The CONVERT function formats the percentage figure so that you get the result in a percentage format.

OUTPUT

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook