Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 15th, 2003, 03:13 AM
Registered User
 
Join Date: Nov 2003
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
 
Old November 15th, 2003, 07:18 AM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old November 16th, 2003, 10:28 PM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old November 17th, 2003, 08:05 AM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old November 17th, 2003, 09:15 AM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old November 17th, 2003, 10:33 PM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old October 6th, 2004, 01:25 PM
Registered User
 
Join Date: Oct 2004
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!!!!
 
Old October 7th, 2004, 11:01 PM
Authorized User
 
Join Date: Apr 2004
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
 
Old October 31st, 2014, 10:42 AM
Registered User
 
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.





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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.