calculate % in survey result - 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

SQL Server 2000 Forums

SQL Server Development (2000)

calculate % in survey result

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-08-17 : 09:49:32

In a survey table, there is a column stored user's selection. For example, there are a, b, c, d, e stored in a column as below:
a, b, c, a, d, d, e, b, e, e. How to calculate the percentage of total? a = 20%, b = 10%, c = 10%, d = 20%, e = 30%.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-17 : 10:05:51

Post table structure, some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-17 : 10:11:47

[code]select col, count(*) * 100.0 / (select count(*) from table)
from table
group by col[/code]

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-17 : 10:11:55

[code]create table #TempData(Selection char(1))
insert into #TempData(Selection)
select 'a'
union all select 'b'
union all select 'c'
union all select 'a'
union all select 'd'
union all select 'd'
union all select 'e'
union all select 'b'
union all select 'e'
union all select 'e'

select Selection,
cast(count(*) as decimal(10,2))/(select count(*) from #TempData)
from #TempData
group by Selection

drop table #TempData
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 10:27:51

Create this function

CREATE FUNCTION dbo.fnGetCharPercent
(
	@WantedChar VARCHAR(2),
	@Options VARCHAR(8000)
)
RETURNS FLOAT
AS

BEGIN
	DECLARE	@Selections INT,
		@Hits FLOAT

	SELECT	@Options = REPLACE(REPLACE(',' + @Options + ',', ' ', ''), ',,', ','), --Remove spaces and double commas
		@Options = REPLACE(@Options, ',', '||'), --replace commas with pipe
		@Selections = DATALENGTH(@Options) - 2 - DATALENGTH(REPLACE(@Options, '||', '')) * 2,
		@Hits = (DATALENGTH(@Options) - DATALENGTH(REPLACE(@Options, '|' + @WantedChar + '|', ''))) / DATALENGTH('|' + @WantedChar + '|')

	RETURN	@Hits / @Selections
END

and use with either select dbo.fnGetCharPercent('e', 'a, b, c, a, d, d, e, b, e, e') or select dbo.fnGetCharPercent('e', SomeColumn)

Peter Larsson
Helsingborg, Sweden

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-17 : 10:52:21

I hope that's not what the OP is trying to do, but it seems the algorithm could be simplified

--Create the function
CREATE FUNCTION dbo.GetCharPercent
	(@CharString varchar(50),
	@SearchChar char(1))
returns decimal(10,2) as
begin
return	1-len(Replace(@CharString, @SearchChar, ''))/cast(len(@CharString) as decimal(10,2))
end
go

--Test the function
declare	@TestString varchar(50)
declare	@TestChar char(1)
set	@TestString = 'a, b, c, a, d, d, e, b, e, e'
set	@TestChar = 'e'

select	dbo.GetCharPercent(Replace(Replace(@TestString, ' ', ''),',', ''), @TestChar)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 11:45:20

It will do if the options are never more than 1 character in length. Otherwise distinguishing between option "ee" and "e" will be messy...

Peter Larsson
Helsingborg, Sweden

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-08-17 : 12:22:04

Thank all of you!
I tested Khtan's way. It works great.

Here is test data:
aaaabbbccccccddeeeee

Here is result:
a:20%, b:15%, c:30%, d:10%, e:25%

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources