need to calculate percentage from table data

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
sqlcuriousAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David L. HansenProgrammer AnalystCommented:
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
sqlcuriousAuthor Commented:
k sure, but please give me a work out, creating a new temp table with this column and doing the calculation there?
0
SANDY_SKCommented:
what you can do is that have a stored procedure / a view to do the required calculations
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jogosCommented:
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
David L. HansenProgrammer AnalystCommented:
Views are excellent for this sort of thing.  The underlying data stays pristine.
0
sqlcuriousAuthor Commented:
thanks the reply but I am getting result as 0 or 100
0
jogosCommented:
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
appariCommented:
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
deightonprogCommented:
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
jogosCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqlcuriousAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.