Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 15th, 2003, 03:13 AM
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL - Computing percentage of total

This will be the easiest question for you to answer, i just know it...

Here's what i'm trying to do:


select
(select count(*) from usability where surveyComplete = 1) as complete,
(select count(*) from usability) as total,
(select count(*) from usability where surveyComplete = 1)/(select count(*) from usability)*100 as percentage

my percentage is always 0

Please help. ty
Reply With Quote
  #2 (permalink)  
Old November 15th, 2003, 07:18 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

COUNT(*) implicitly returns its result as an integer. Thus, you are doing integer arithmetic in the divide expression, and that result is also an integer.

CAST the expressions to FLOAT, as:
Code:
SELECT ...
CAST(select count(*) from usability where surveyComplete = 1) as FLOAT)
/(select count(*) from usability)*100.0 as percentage
Note that using 100.0 will implicitly CAST the second expression to a FLOAT.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #3 (permalink)  
Old November 16th, 2003, 10:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

It is so simple. I had a similar problem sometime back, dealing with decimals and posted the same here. As Jeff suggested, I had to do it from the frontend to get the exact values.

The calculation you have done is right and you have do a slight alteration in that. Unfortunately SQL does not deal well with decimals.

In your case as you divide a lesser number with greater number it gives a values less than 0(starting with .xxxx) which SQL rounds of to its nearest whole number and so you get ZERO, which is multiplied with 100 to get the same.

Taking that into consideration, just move the "* 100" before the division operator. You would get the right percentage, but not with delimals always.

May be that is something you have to do from your frontend.

select
(select count(*) from usability where surveyComplete = 1) as complete,
(select count(*) from usability) as total,
((select count(*) from usability where surveyComplete = 1) *100) /(select count(*) from usability) as percentage

Cheers,


-Vijay G
Reply With Quote
  #4 (permalink)  
Old November 17th, 2003, 08:05 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by happygv


...
Unfortunately SQL does not deal well with decimals.
SQL can deal with decimals just fine. I wouldn't want to use it for any "heavy" mathematical calculations, but computing a percentage is well within its capabilities.
Quote:
quote:
In your case as you divide a lesser number with greater number it gives a values less than 0(starting with .xxxx) which SQL rounds of to its nearest whole number and so you get ZERO, which is multiplied with 100 to get the same.
No. What is happening here, as I stated in my prior post, is that the operands in the expression are all integer type, so integer arithmetic is being performed. No rounding is being performed, but rather truncation of the results to integers is occurring because the expression is an integer type.
Quote:
quote:
Taking that into consideration, just move the "* 100" before the division operator. You would get the right percentage, but not with delimals always.
No. Unless the expression is forced to be represented as a non-integral type, the results will still be "wrong". (Actually, the results aren't really wrong, they are just being represented to an inadequate precision...)
Quote:
quote:
May be that is something you have to do from your frontend.
May be. But it can be computed correctly in SQL, as my prior post stated.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #5 (permalink)  
Old November 17th, 2003, 09:15 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I don't mean to jump on the 'have a go at Vijay' band wagon, however in your post you said
Quote:
quote:...it gives a values less than 0...
Actually it is not less than 0, it is less than or equal to 1 and greater than or equal to 0.

As an example, say you are dividing 54 by 96 then what you are getting is 0.5625, the 2 values are integers (whole numbers) so it converts this return value to a whole number.

What is happening in my example is it truncates the numbers to the right of the decimal point, therefore always returning 0 (unless the two numbers you are dividing were equal in which case it would return 1). You are then multiplying this number by 100 giving you 0 again (or 100 if you were dividing for example 96 by 96).

Here are a couple of examples:

0 / 96 = 0; 0 * 100 = 0%
54 / 96 = 0.5625 which converts to 0; 0 * 100 = 0%
96 / 96 = 1; 1 * 100 = 100%

Regards
Owain Williams
Reply With Quote
  #6 (permalink)  
Old November 17th, 2003, 10:33 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jeff,

Thanks for your comments/explanation.

Owain,
"Actually it is not less than 0, it is less than or equal to 1 and greater than or equal to 0."

That was a typo(non-co-operation between the mind and the hand while typing ;) ). Nothing wrong in pointing out the errors.

Anyways thanks guys for being so nice:).


-Vijay G
Reply With Quote
  #7 (permalink)  
Old October 6th, 2004, 01:25 PM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am trying to do something similar to this--however, I need percentage of totals for EACH distinct country in a database

How can I do this?

Ex)
country total pct_total
US 9 0.33
US 9 0.33
US 9 0.33
FR 9 0.33
FR 9 0.33
FR 9 0.33
IT 9 0.33
IT 9 0.33
IT 9 0.33

I thank you in advance for any help!!!!
Reply With Quote
  #8 (permalink)  
Old October 7th, 2004, 11:01 PM
Authorized User
 
Join Date: Apr 2004
Location: Boise, ID, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You would probably want to use ratio_to_report here

Code:
  1  select ename,
  2         deptno,
  3         sal,
  4         ratio_to_report(sal) over (partition by deptno) pct
  5*   from emp
SCOTT @ JCAVE10G Local> /

ENAME          DEPTNO        SAL        PCT
---------- ---------- ---------- ----------
CLARK              10       2450        .28
KING               10       5000 .571428571
MILLER             10       1300 .148571429
SMITH              20        800 .073563218
ADAMS              20       1100 .101149425
FORD               20       3000 .275862069
SCOTT              20       3000 .275862069
JONES              20       2975 .273563218
ALLEN              30       1600 .170212766
BLAKE              30       2850 .303191489
MARTIN             30       1250 .132978723
JAMES              30        950  .10106383
TURNER             30       1500 .159574468
WARD               30       1250 .132978723

14 rows selected.
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
Reply With Quote
  #9 (permalink)  
Old October 31st, 2014, 10:42 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2014
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Smile Thanks for this post

It helped me with a task.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem computing in DataGridView 4x4 ADO.NET 1 January 19th, 2007 02:20 PM
SQL for records that contain a percentage sign lukeb Oracle 2 June 12th, 2006 08:04 PM
how to calculate the percentage in sql yvr238 SQL Server 2000 1 May 13th, 2006 03:28 AM
General 'Computing' discussion forum crmpicco HTML Code Clinic 2 August 31st, 2005 08:19 AM
total columns in sql query nlicata SQL Server ASP 1 August 4th, 2003 06:33 PM



All times are GMT -4. The time now is 09:53 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.