Solved

need to calculate percentage from table data

Posted on 2012-03-13
11
242 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server computed columns 11 29
SSRS 2013 - Overlapping reports 2 19
CPU high usage when update statistics 2 28
SQL - Update field defined as Text 6 15
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
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.
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…

813 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

10 Experts available now in Live!

Get 1:1 Help Now