trying to get percentages and I am lost (help)

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

SQL Server 2000 Forums

SQL Server Development (2000)

trying to get percentages and I am lost (help)

Author

Topic

csphard
Posting Yak Master

113 Posts

Posted - 2005-06-01 : 01:59:25

I am supplying table and data. Please help thanks

CREATE TABLE college_survey
(
srv_currentChildcare varchar (50)
)

insert into college_survey(srv_currentChildcare) values ('licensed family')
insert into college_survey(srv_currentChildcare) values ('unLicensed')
insert into college_survey(srv_currentChildcare) values ('unLicensed')
insert into college_survey(srv_currentChildcare) values ('licensed family')
insert into college_survey(srv_currentChildcare) values ('unLicensed')
insert into college_survey(srv_currentChildcare) values ('unLicensed')
insert into college_survey(srv_currentChildcare) values ('unLicensed')
insert into college_survey(srv_currentChildcare) values ('unLicensed')
insert into college_survey(srv_currentChildcare) values ('unLicensed')
insert into college_survey(srv_currentChildcare) values ('licensed childcare')
insert into college_survey(srv_currentChildcare) values ('licensed childcare')
insert into college_survey(srv_currentChildcare) values ('Not Sure')
insert into college_survey(srv_currentChildcare) values ('Not Sure')
insert into college_survey(srv_currentChildcare) values ('unLicensed')

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-01 : 02:11:16

Hiya

The trick is casting all your ints to floats before you work with them, otherwise everything gets cast as an int.

SELECT
srv_currentChildcare,
Count(*),
CAST(Count(*) as float) * ( CAST( 100 as float) / Cast( ( SELECT Count(*) FROM college_survey ) as float))

FROM

college_survey

GROUP BY srv_currentChildcare

Damian
Ita erat quando hic adveni.

P.S. Thank you for providing DDL and DML. As you can see it makes helping much easier

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-01 : 02:15:28

--set esp on

declare @tot decimal
select @tot=count(*) from @college_survey
select (count(*)/@tot) * 100 as percentage
,srv_currentchildcare
from @college_survey
group by srv_currentchildcare

--set esp off

--------------------
keeping it simple...

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-01 : 02:18:09

Or you can do what Jen did, which is much neater

Damian
Ita erat quando hic adveni.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-01 : 02:21:48

quote:
Originally posted by Merkin

Or you can do what Jen did, which is much neater

Damian
Ita erat quando hic adveni.

is that a compliment Damian? Drink a beer for me will you...

--------------------
keeping it simple...

csphard
Posting Yak Master

113 Posts

Posted - 2005-06-01 : 02:50:37

thanks for your help

question.

both answers give the following percentages

licensed childcare 2 14.285714285714286
licensed family 2 14.285714285714286
Not Sure 2 14.285714285714286
unLicensed 8 57.142857142857146

this does total 100 %

However I wanted get 2 character numbers that total 100%

14+14+14+57 = 99

That may be good enough, but if someone could tell me how to solve this I would appreciate it.

Thanks for your help again

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-01 : 02:53:40

quote:
Originally posted by csphard

thanks for your help

question.

both answers give the following percentages

licensed childcare 2 14.285714285714286
licensed family 2 14.285714285714286
Not Sure 2 14.285714285714286
unLicensed 8 57.142857142857146

this does total 100 %

However I wanted get 2 character numbers that total 100%

14+14+14+57 = 99

That may be good enough, but if someone could tell me how to solve this I would appreciate it.

Thanks for your help again

the only way is to add 1 to 57, which means you have to decide how you are going to round off the numbers, percentages do come in 2 or 3 decimal places

--------------------
keeping it simple...

csphard
Posting Yak Master

113 Posts

Posted - 2005-06-01 : 02:58:46

Then I could just pick up the whole numbers to the left of the decimal because this gives me 99. If i pick up 2 decimal places to the right I still get 99.98

I see it will not make much of a difference.

I going to truncate it and pick to whole number an see what that yields

thanks for all your help.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-01 : 03:03:53

did you know there's a function called ceiling?

HTH

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources