SQL

ace2e · November 15th, 2003, 03:13 AM

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

Jeff Mason · November 15th, 2003, 07:18 AM

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

happygv · November 16th, 2003, 10:28 PM

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

Jeff Mason · November 17th, 2003, 08:05 AM

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

owain · November 17th, 2003, 09:15 AM

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

happygv · November 17th, 2003, 10:33 PM

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

bsg3433 · October 6th, 2004, 01:25 PM

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!!!!

Justin Cave · October 7th, 2004, 11:01 PM

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

BillD2 · October 31st, 2014, 10:42 AM

It helped me with a task.