Solved

need to calculate percentage from table data

Posted on 2012-03-13
11
241 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
  • 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
 
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL Where clause for Date with CASE - what is wrong? 11 71
SQL Query stumper 3 36
SQL - insert empty rows into output results 11 22
Sql Query 4 0
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

911 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

16 Experts available now in Live!

Get 1:1 Help Now