Solved

need to calculate percentage from table data

Posted on 2012-03-13
11
250 Views
Last Modified: 2012-03-28
Hi experts,
I need to calculate percentage from a table with data as below
Cust_no  On-time
1                O
1                N
1                O
2                N
2                N
2                O
2                O

So I need to add a column called PercOntime = 100X(count(O)) / Total count

For the column On-time , flag O- ontime payments and  Flag N- not on time.
Please help with the logic
0
Comment
Question by:sqlcurious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 37716486
Bad idea.  If this is Excel, no problem.  If you are doing this in the table itself you are violating good normalization rules.

Standard example is: Age and Birthdate in the same table is bad because one is a direct calculation of the other.  You could end up with them getting out of sync.  It is just unnecessary.  Do the calculation in the application(s) that use the table.
0
 

Author Comment

by:sqlcurious
ID: 37716655
k sure, but please give me a work out, creating a new temp table with this column and doing the calculation there?
0
 
LVL 11

Expert Comment

by:SANDY_SK
ID: 37716669
what you can do is that have a stored procedure / a view to do the required calculations
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 25

Expert Comment

by:jogos
ID: 37716706
View or stored procedure

The basic select for your % per customer.

select Cust_no,   100 * (sum(case when On-time= 'O' then 1 else 0 end) / count(Cust_no)) as Pct
from dbo.tableA
group by Cust_no

Open in new window

0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 37716737
Views are excellent for this sort of thing.  The underlying data stays pristine.
0
 

Author Comment

by:sqlcurious
ID: 37716816
thanks the reply but I am getting result as 0 or 100
0
 
LVL 25

Expert Comment

by:jogos
ID: 37716868
How to debug .... (or how to start builiding it yourself)  start part by part implementing the functions you want and test step by step

Is the Case ok

select Cust_no,   On-time,
case when On-time= 'O' then 1 else 0 end,
from dbo.tableA

What count and sum + together in calculation

select Cust_no,  
count(Cust_no),
sum(case when On-time= 'O' then 1 else 0 end),
100 * (sum(case when On-time= 'O' then 1 else 0 end) / count(Cust_no)) as Pct
from dbo.tableA
group by Cust_no
0
 
LVL 39

Expert Comment

by:appari
ID: 37718194
your current query is doing integer division, need to convert numbers to decimal/float.
try this
select Cust_no,   100.0 * (sum(case when On-time= 'O' then 1 else 0 end) * 1.0 / count(Cust_no)) as Pct
from dbo.tableA
group by Cust_no

Open in new window

0
 
LVL 18

Expert Comment

by:deighton
ID: 37719529
try

select Cust_no,   100.0 * (sum(case when On-time= 'O' then 1.0 else 0.0 end) / count(Cust_no)) as Pct
from dbo.tableA
group by Cust_no
0
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37719579
Or  by relocating the ()
select Cust_no
,   (100 * sum(case when On-time= 'O' then 1 else 0 end)) / count(Cust_no) as Pct_int
,   (100.0 * sum(case when On-time= 'O' then 1 else 0 end)) / count(Cust_no) as Pct
from dbo.tableA
group by Cust_no

Open in new window

0
 

Author Closing Comment

by:sqlcurious
ID: 37778755
thanks
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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