Percentage of columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Percentage of columns

Author

Topic

crazyco
Starting Member

30 Posts

Posted - 2011-04-27 : 09:22:44

How do I work out the percentage of data in columns? For example, I have a query:

select sex, dob, firstDiagnosed, injectingInsulin, insulinRegime, measureBG
from Patient p LEFT JOIN Region r on p.Region = r.RegionID
LEFT JOIN Questionnaire q on q.PatientID = p.PatientID

Not all of the fields in the Questionnaire table have fields so I need to know the percentage of each column that does have data, i.e.

Sex 80%
dob 45%
firstDiagnosed 10%

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-27 : 09:36:42

SELECT SUM(CASE WHEN sex is null THEN 1 ELSE 0 END)*1.0/count(*) as NotGettingAnySex
,SUM(CASE WHEN dobis null THEN 1 ELSE 0 END)*1.0/count(*) as NoDob

etc.

Jim

Everyday I learn something that somebody else already knew

crazyco
Starting Member

30 Posts

Posted - 2011-04-27 : 10:36:14

Thanks Jim, that's what I was after, only a slight tweek

SELECT SUM(CASE WHEN sex is null THEN 0 ELSE 1 END)*100/count(*) as gender,
SUM(CASE WHEN dob is null THEN 0 ELSE 1 END)*100/count(*) as dob, etc..

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-27 : 10:50:32

I think you'll need the decimal digits... otherwise, it will be an int/int which will equal... int.

Unless... thats what you want of course.

quote:
Originally posted by crazyco

Thanks Jim, that's what I was after, only a slight tweek

SELECT SUM(CASE WHEN sex is null THEN 0 ELSE 1 END)*100.0/count(*) as gender,
SUM(CASE WHEN dob is null THEN 0 ELSE 1 END)*100.0/count(*) as dob, etc..

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources