Calculating Percent of Total in a SQL Select Statement

I am always amazed at how SQL server provides a lot of calculations and logic built right into a select statement. Most of the time (if you are like me) you spend 85% of your time writing basic Select statements with a variety of where clauses. However last week I came across a need to get the percent of total for 1000’s of records. In other words assume you have this data.

Name Age
David 34
Jeff 19
Steve 18
Bill 19
Lin 40
Heather 41
Sarah 37
Tom 37
Linda 37
Jennifer 35
Susan 20
Laura 19
Jake 18

Using this data I generated a sample table with data to perform an optimization exercise. Here are the details.

CREATE TABLE TempTable([Name] varchar(20),Age Int);

INSERT INTO TempTable ([Name], Age) VALUES ('David',34)
INSERT INTO TempTable ([Name], Age) VALUES ('Jeff',19)
INSERT INTO TempTable ([Name], Age) VALUES ('Steve',18)
INSERT INTO TempTable ([Name], Age) VALUES ('Bill',19)
INSERT INTO TempTable ([Name], Age) VALUES ('Lin',40)
INSERT INTO TempTable ([Name], Age) VALUES ('Heather',41)
INSERT INTO TempTable ([Name], Age) VALUES ('Sarah',37)
INSERT INTO TempTable ([Name], Age) VALUES ('Tom',37)
INSERT INTO TempTable ([Name], Age) VALUES ('Linda',37)
INSERT INTO TempTable ([Name], Age) VALUES ('Jennifer',35)
INSERT INTO TempTable ([Name], Age) VALUES ('Susan',20)
INSERT INTO TempTable ([Name], Age) VALUES ('Laura',19)
INSERT INTO TempTable ([Name], Age) VALUES ('Jake',18)

--Method #1
Declare @totalcount integer
Set @totalcount = (select count(*) as totalrec from TempTable)

Select Age, num, Total, (cast(num as float)/Total)*100 as PctTotal from (
Select Age,count(*) as num, @totalcount as total from TempTable tb1 Group by Age) ee
------------------------------------------------

Drop Table TempTable

This will give you the right answer and is a pretty common way I have seen a select statement like this written. But using Over(Partition By …) you can achieve some pretty amazing performance gains. By changing the Select to this instead….

--Method #2
select distinct
Age,
  count(Age) over() AS TOTALValue,
  count(Age) over(partition by age),
  --Calculate Percentage
  cast((count(Age) over(partition by age)/(count(age) over()*1.000)) as float)*100 as Percentage
from TempTable
-------------------------------------------------

There is a major improvement in performance as well as it is easier to understand.

I used about 1,500 records and with the help of Toad for SQL here are the results.

Scenario #1 : 2.662 seconds to run
Scenario #2 : 0.017 seconds to run

You can see by the results that taking time to better understand your options and altering your select statement you can not only get percent of total using Selects but do it in a very concise and optimized way.

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


6 − 3 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>