Counting how many precents a value represents

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)

Counting how many precents a value represents

Author

Topic

youngster
Starting Member

10 Posts

Posted - 2007-11-16 : 08:39:10

Hey!

I have this column that could have different values (I don't know how many, but too many) and I want to get each such value and number_of_occurens*100/number_of_rows_in_the_table (GROUP BY i suppose). So say we have at table where with only one column and the rows:
a
g
b
b
h
d
a
a
a
g

then I want:
a | 40%
b | 20%
g | 20%
d | 10%
h | 10%
(the most occuring at the top, but it isn't that important)

Thanks.

youngster
Starting Member

10 Posts

Posted - 2007-11-16 : 08:45:32

I forgot. I have this

SELECT detaillabel, 100*COUNT(*)/X AS Precent
FROM dbo.dbA
WHERE detaillabel IS NOT NULL
GROUP BY detaillabel
ORDER BY Precent

But I don't how you make "X" dynamic

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-16 : 08:48:09

messy solution but off the top of my head...

declare @t table (col char(1))

insert into @t
select 'a'
union all select 'a'
union all select 'b'
union all select 'b'
union all select 'b'
union all select 'b'
union all select 'c'
union all select 'c'
union all select 'c'
union all select 'c'
union all select 'a'
union all select 'b'
union all select 'a'
union all select 'd'


select col, cast(count(*)*100.0/(select count(*) from @t)*1.0 as decimal(4,1))
from @t
group by col

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-16 : 08:49:44

Like this:


-- Prepare sample data
declare @t table
(
	a varchar(1)
)

insert @t
select 'a' union all
select 'g' union all
select 'b' union all
select 'b' union all
select 'h' union all
select 'd' union all
select 'a' union all
select 'a' union all
select 'a' union all
select 'g'

-- Main query
select a, (count(*) * 100)/(select count(*) from @t) as Percentage
from @t
group by a
order by 2 desc

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources