• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

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
0
sqlcurious
Asked:
sqlcurious
  • 3
  • 3
  • 2
  • +3
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
deightonCommented:
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
 
sqlcuriousAuthor Commented:
thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now