Solved

Working out percentages in sql

Posted on 2010-09-17
14
477 Views
Last Modified: 2012-05-10
Hi everyone

I have a problem with the code below and I cannot see what is wrong with it.  It compiles but the results are not what they should be.
select week_no,dept_code,count(*) as 'total registers', count(absence_code) as 'registers marked',

cast(((count(absence_code) / count(*) ) * 100) as decimal (10,2)) as '% marked',

((count(*))- (count(absence_code))) as 'registers not marked',

cast(((((count(*))- (count(absence_code))) / count(*) ) * 100) as decimal (10,2)) as '% not marked'

Open in new window


I have total registers, then registers marked, % marked registers, registers not marked and percentage not marked.

The two percentage fields are not working unless the results is 100% if not the answer shown is 0.00

Can anyone see what I am doing wrong thanks
0
Comment
Question by:lisa_mc
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 33699911
Hi at a quick look,

'Could' the reason is because are using decimal eg (10,2)  if it is rounding to 2 decimal places, it will never end up as 100% exactly will it?
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33699920
your also using count not sum(), is this correct?
0
 
LVL 3

Author Comment

by:lisa_mc
ID: 33699930
Hi

Even if I take that out it still doesn't work

Is the logic correct?

One thing im thinking sometimes the value for registers marked or registers not marked could be zero would that cause a problem as you could be doing this 0/12 * 100

0
 
LVL 3

Author Comment

by:lisa_mc
ID: 33699938
it has to be count as I am working from a student level
0
 
LVL 3

Author Comment

by:lisa_mc
ID: 33699953
example of my results at the min

if I have 4 registers with none marked then the percentage marked = 0.00
and the percentage not marked = 100.00

but if I have 15 registers, 2 are marked and 13 are unmakred then my results show
percentage marked = 0.00
percentage unmarked = 0.00
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33700006

Try adding in a check for division by Zero

 
where count(*) <>0
and count(absence_code)  <> 0
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33700068
no worries, glad you got it sorry wasnt much more help im getting ready for work, an im running late too !! ahh cya ;)
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33700104
what about this;
select week_no 

     , dept_code

     , count(*) as 'total registers'

     , count(absence_code) as 'registers marked'

     , cast( cast( count(absence_code)as decimal/10,2) / count(*) * 100.00 ) as decimal (10,2)) as '% marked'

     ,((count(*))- (count(absence_code))) as 'registers not marked'

     , cast(((( cast(count(*)- count(absence_code) as decimal/10,2) ) / count(*) ) * 100.00) as decimal (10,2)) as '% not marked'

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33700600
The indicated comment, http:#a33699953, does not answer the question.The real answer is that at least one of the operands has to be converted to a non-integer, or else the result will use integer division.  Thus, angelIII's comment http:#a33700104 is the answer.Patrick
0
 
LVL 3

Accepted Solution

by:
lisa_mc earned 0 total points
ID: 33700880
hi matthewspartick

I put up an explanation when I accept my own solution as the answer which was done before 04:16am because at this time kingjely said no worries

my reasoning was that I found this website

http://en.allexperts.com/q/MS-SQL-Server-1801/Dividing.htm

which answered my problem before angelll had left a comment
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33700963
lisa_mc,With respect, that page you linked to still does not answer the question.Your problem was that you tried to divide the results of two COUNT expressions first, and then convert the quotient into a decimal data type.  That does not work because, as the dividend and divisor are both integers, the quotient will also be an integer.angelIII's comment showed that at least one of the operands has to be implicitly or explicitly converted to a non-integer first.Patrick
0
 
LVL 3

Author Comment

by:lisa_mc
ID: 33701135
Hi matthewspatrick

WIth respect as well I have attached a line of code from the webpage

But if you make that fix and run, you'll still get 0. Why? The 2nd problem: SQL Server sees
10/22 and says "Aha! An expression of undeclared type. I'll just take the type of the 1st thing I come to as the type to use". So it sees the 10 and say "INTEGER". So 10/22 = 0.454545, which when rounded to an INTEGER is 0.

The fix for the 2nd problem is to make the 10 a decimal or float:
SELECT @Test = 10.0/22
or
SELECT @Test = CAST(10 AS NUMERIC(10.1))/22

That should do it!

If this doesn't fix my problem then why is my problem fixed. Does the text above not state that you should make 10 a decimal or float first because if not stated then sql will take the type of the first statement used which in my case was an integer.

I hope this clears up the confusion of why im accepting my own answer, if not maybe you could explain why
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33701139
matthewspatrick,

 with due respect, but you are wrong there.
 the link DOES show and explain why the 10/22 returns 0, and how to solve it.
 it does not explain that COUNT(...) does return a integer, but that was visibly not blocking the asker understanding and solving the issue, even before my post

angel eyes
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33701208
Very well, my apologies for delaying the outcome.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to do this MS SQL script? 11 48
Query question 4 30
SQL Query Syntax Error 9 33
Oracle Subquery bad Join 11 43
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now