Calculating Percentages: - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Calculating Percentages:

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2011-12-15 : 18:52:17

Hi,

I'm having a problem calculating some percentages on columns and I'm not sure what I'm doing wrong: In the table below, I'm trying to calculate the Percent of Q1 based on the total so for User = Joe, it should be as follows 50/Sum(50 + 10 + 75) = 37%. When I try to calculate this in SQL, i'm getting 0 in the percent of Q1 column:

select User, Sum(Q1 + Q2 + Q3)/Q1 as Percent of Q1
From Table1
where User = Joe -- this query returns zero when it should return 37%

User: Q1 Q2 Q3 PERCENT OF Q1
Joe 50 10 75
Bill 2 8 40
Lisa 10 5 20

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-15 : 19:01:21

Probably just need to switch to decimal:

select User, Sum(Q1 + Q2 + Q3)/Q1*1.0 as Percent of Q1
From Table1
where User = Joe

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-15 : 19:02:37

Read this for more information: http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

joe8079
Posting Yak Master

127 Posts

Posted - 2011-12-15 : 19:18:20

thanks, I'll give that shot

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-15 : 19:40:58

should multiply by 100.0 if you want percentage


select User, Sum(Q1 + Q2 + Q3)/Q1 * 100.0 as [Percent of Q1]
From Table1
where User = 'Joe'

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources