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

Calculate percents based on value in a column??

Hi,

I need to write a query that will calculate three diff perecentages and I am unsure how to do so.

I have a table that with a column called "COMPLETED".  I need to write a query that will pull all records from this table and tell me what percent have a value of "Y" in this column or a value of "N" or if its null or empty, then that means it has not been started.

So I have 10 records in this table, each has a value of Y, N, or NULL or empty for this column.  Out of the total records returned, I need to know what percent have a value of "Y", and what percent have a valuer of "N", and what percent have a value of NULL or empty string.  I guess they columns can be aliased as percentComplete, percentIncomplete, percentNotStarted or something.

Any help appreciated........

-ws

0
Westside2004
Asked:
Westside2004
  • 8
  • 6
  • 3
  • +1
2 Solutions
 
gpompeCommented:
select sum(case when completed='Y' then 1 else 0 end)/count(*) percentComplete,
sum(case when completed='N' then 1 else 0 end)/count(*) percentIncomplete,
sum(case when isnull(completed,'')='' then 1 else 0 end)/count(*) percentNotStarted
from your_table
0
 
QPRCommented:
select count(*) / count(completed) as percentComplete
from YourTable
where completed = 'Y'
union select
select count(*) / count(completed) as percentInComplete
from YourTable
where completed = 'N'
union select
select count(*) / count(completed) as percentNotStarted
from YourTable
where isNull(completed,'-') = '-'

untested off top of head!
0
 
Westside2004Author Commented:
Hi,

This line seems to throw an error:

sum(case when isnull(completed,'')='' then 1 else 0 end)/count(*) percentNotStarted

Are those double quotes?

-ws
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
QPRCommented:
single you are treating nulls as an empty string and then comparing them to an empty string
0
 
David ToddSenior DBACommented:
Hi,

>> Are those double quotes?
They are two single quotes

That can also be written

sum( case when  completed is null then 1 else 0 end ) / count( 1 ) percentNotStarted

Regards
  David

PS Count( 1 ) is faster than Count( * ) which implicitly returns the whole row to count, not just that a row exists, which then counts a row with one column and the value 1.
0
 
Westside2004Author Commented:
Hi,

Here is my full query, no error now when I added single quotes, but the results come back with zeroes, I get 1 record which is right, but zeroes for everything.

SELECT             
SUM(CASE WHEN sp.completed ='N' then 1 else 0 end)/count(*)AS percentComplete,
SUM(CASE WHEN sp.completed ='Y' then 1 else 0 end)/count(*) AS percentComplete,
SUM(CASE WHEN ISNULL(sp.completed,'')='' THEN 1 ELSE 0 end)/count(*)percentNotStarted
FROM tbl_Schools s
INNER JOIN tbl_xref_table x ON x.school_ID = s.school_ID
LEFT OUTER JOIN      school_profiles sp ON s.school_ID = sp.school_ID
WHERE s.inactive = 0
AND x.pp_id = 32

Any help appreciated. I dont think I need to join on tbl_schools as I am not getting any info from there anyways.  

The tab

0
 
gpompeCommented:
SELECT            
1.0*SUM(CASE WHEN sp.completed ='N' then 1 else 0 end)/count(*)AS percentComplete,
1.0*SUM(CASE WHEN sp.completed ='Y' then 1 else 0 end)/count(*) AS percentComplete,
1.0*SUM(CASE WHEN ISNULL(sp.completed,'')='' THEN 1 ELSE 0 end)/count(*)percentNotStarted
FROM tbl_Schools s
INNER JOIN tbl_xref_table x ON x.school_ID = s.school_ID
LEFT OUTER JOIN      school_profiles sp ON s.school_ID = sp.school_ID
WHERE s.inactive = 0
AND x.pp_id = 32
0
 
gpompeCommented:
OR if you want the percents

SELECT            
100.0*SUM(CASE WHEN sp.completed ='N' then 1 else 0 end)/count(*)AS percentComplete,
100.0*SUM(CASE WHEN sp.completed ='Y' then 1 else 0 end)/count(*) AS percentComplete,
100.0*SUM(CASE WHEN ISNULL(sp.completed,'')='' THEN 1 ELSE 0 end)/count(*)percentNotStarted
FROM tbl_Schools s
INNER JOIN tbl_xref_table x ON x.school_ID = s.school_ID
LEFT OUTER JOIN      school_profiles sp ON s.school_ID = sp.school_ID
WHERE s.inactive = 0
AND x.pp_id = 32
0
 
Westside2004Author Commented:
@gpompe -

Thanks for the reply, that got me some numbers back, the numbers appear wrong.

There are a total of 57 records in the xref table.  Therefore any calculations should be based on that number.  Out of the 57 records, a subset appear in table "school_profiles"  A record gets add to table "School_Profiles" when a profile is started.  Right now I have 3 records in this table, and they all have 'N' for the "Completed" column, so the other 54 do not have records in this table, but should be calculated as part of the 57 records.

So I have three tables here

tbl_Schools
tbl_xref_table (57 records)
tbl_School_Profiles (3 records with value of 'N' in "completed column"

HTH,

-ws
0
 
Westside2004Author Commented:
right now the result set says:

percentIncomplete    percentComplete  percentNotStarted
22.033898305084      .000000000000      77.966101694915

3 / 57 * 100 should be around 5.27% I believe, not 22% which is what I am getting.

-ws


0
 
David ToddSenior DBACommented:
Hi,

Suggest using float literals instead of integer literals if going for a floating point result.

That is:
sum( case when  completed is null then 1 else 0 end ) / count( 1 ) percentNotStarted

becomes
cast( sum( case when  completed is null then 1.0 else 0.0 end ) as float ) / cast( count( 1 ) as float) as percentNotStarted

Also bear this in mind: The order of the operations can have a profound effect on accuracy. That is, do all the multiplication prior to the division.

3 / 57 * 100 should be 3.0 * 100.0 / 57.0

HTH
  David
0
 
Westside2004Author Commented:
@ dtodd - Thanks for the suggestion dtodd, I modified it that way, but still the numbers are wrong and I am not sure why.

-ws
0
 
David ToddSenior DBACommented:
Hi,

Run this query to double check the maths:

SELECT            
100.0*SUM(CASE WHEN sp.completed ='N' then 1 else 0 end)/count(*)AS percentInComplete,
100.0*SUM(CASE WHEN sp.completed ='Y' then 1 else 0 end)/count(*) AS percentComplete,
100.0*SUM(CASE WHEN ISNULL(sp.completed,'')='' THEN 1 ELSE 0 end)/count(*)percentNotStarted

-- counts
, SUM(CASE WHEN sp.completed ='N' then 1 else 0 end) as CountInComplete
, SUM(CASE WHEN sp.completed ='Y' then 1 else 0 end) as CountCompleted
, SUM(CASE WHEN ISNULL(sp.completed,'')='' THEN 1 ELSE 0 end ) as CountNotStarted
, count( 1 ) as totalno
FROM tbl_Schools s
INNER JOIN tbl_xref_table x ON x.school_ID = s.school_ID
LEFT OUTER JOIN      school_profiles sp ON s.school_ID = sp.school_ID
WHERE s.inactive = 0
AND x.pp_id = 32

I found that in a query above both the complete and incomplete lines has the same expression alias. I'd expect that to cause an error. Otherwise that could explain your results.

Regards
  David
0
 
Westside2004Author Commented:
Hi,

I got it.... it was the x.pp_id, should be sp.pp_id

That fixed it.....

Thanks for the help!
0
 
Westside2004Author Commented:
Is there a way to round the percents in SQL??  That way i always get just two digits after the decimal?

-ws
0
 
gpompeCommented:
When you said 57 rows, Are you taking in account the where clause ?

How many rows return this query ?

select *
FROM tbl_Schools s
INNER JOIN tbl_xref_table x ON x.school_ID = s.school_ID
WHERE s.inactive = 0
AND x.pp_id = 32
0
 
gpompeCommented:
Use: Round(sum(....)/Count(1),2)
0
 
Westside2004Author Commented:
Hi,

As mentioned before I did not need to have "tbl_Schools" even involved, originally this query was from another I wrote, but it was not needed.   I ended up with a mix of both of your solutions so I split the points.  Final query

SELECT             
100.0 * cast( sum( case when sp.completed = 'N' then 1.0 else 0.0 end ) as float ) / cast( count( 1 ) as float) as percentIncomplete,
100.0 * cast( sum( case when sp.completed = 'Y' then 1.0 else 0.0 end ) as float ) / cast( count( 1 ) as float) as percentComplete,
100.0 * cast( sum( case when sp.completed is null then 1.0 else 0.0 end ) as float ) / cast( count( 1 ) as float) as percentNotStarted
FROM tbl_xref_table x
LEFT JOIN school_profiles sp ON sp.school_ID = x.school_ID
AND sp.pp_id = 32  ( AS OPPOSED TO x.pp_id)

That seemed to work for me...

-ws
0
 
gpompeCommented:
If it worked for you, that your query
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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