We help IT Professionals succeed at work.

# Calculate percents based on value in a column??

on
Medium Priority
405 Views
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

Comment
Watch Question

## View Solutions Only

Commented:
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

Commented:
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,'-') = '-'

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

Commented:
single you are treating nulls as an empty string and then comparing them to an empty string
CERTIFIED EXPERT

Commented:
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.

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

Commented:
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
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

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

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

CERTIFIED EXPERT

Commented:
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

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
CERTIFIED EXPERT
Commented:
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

Commented:
Hi,

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

That fixed it.....

Thanks for the help!

Commented:
Is there a way to round the percents in SQL??  That way i always get just two digits after the decimal?

-ws

Commented:
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

Commented:
Use: Round(sum(....)/Count(1),2)

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

Commented:
If it worked for you, that your query
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile