|
|
 |
| 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 .
|
 |
|
|
 |
November 15th, 2003, 03:13 AM
|
|
Registered User
|
|
Join Date: Nov 2003
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
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
|
|
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 |
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
|
|
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
|
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
|
|
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
|
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
|
|
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 |
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
|
|
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
|
October 6th, 2004, 01:25 PM
|
|
Registered User
|
|
Join Date: Oct 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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!!!!
|
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
|
|
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 |
October 31st, 2014, 10:42 AM
|
|
Registered User
|
|
Join Date: Oct 2014
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thanks for this post
It helped me with a task.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
