Calculate percents based on value in a column??

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

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!

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

single you are treating nulls as an empty string and then comparing them to an empty string

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.

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

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

@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

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

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

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

-ws

Hi,

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

That fixed it.....

Thanks for the help!

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

-ws

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

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

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

If it worked for you, that your query