Faster way to calculate percentage?

I currently use this method to come up with a percentage:

declare @height table
(
    UserId int,
    tall bit
)
insert into @height
select  1, 1 union all
select  2, 1 union all 
select 6, 0 union all
select  3, 0 union all 
select 7, 0 union all
select  4, 1 union all 
select 8, 0 union all
select  5, 0

declare @all decimal(8,5)

select 
    @all = count(distinct UserId)
from @height

select 
    count(distinct UserId) / @all Pct
from @height
where tall = 1  

Result: 0.375000000

Is there a better performing way to do this? As you can see the @height

table is hit twic开发者_运维技巧e.

Thanks!

This allows you to hit the table only once, and gives you the same result for your given dataset.

declare @height table
(
    UserId int,
    tall bit
)
insert into @height
select  1, 1 union all
select  2, 1 union all 
select 6, 0 union all
select  3, 0 union all 
select 7, 0 union all
select  4, 1 union all 
select 8, 0 union all
select  5, 0


select SUM(convert(decimal(8,5), tall)) / convert(decimal(8,5), COUNT(*)) Pct
from @height

Depending on your requirements, this might work for duplicate userids. At least it gives the same result as yours does.

select SUM(convert(decimal(8,5), tall)) / convert(decimal(8,5), COUNT(distinct userid)) Pct
from 
    (select distinct UserId, tall
    from @height) t

Here is an alternative query that produces your expected results. I don't know how the performance of this query compares to others, but I suspect it would be easy enough for you to test this.

declare @height table
(
    UserId int,
    tall bit
)
insert into @height
select  1, 1 union all
select  2, 1 union all 
select  4, 1 union all 

select  3, 0 union all 
select  5, 0 union all
select  6, 0 union all
select  7, 0 union all
select  8, 0

Select 1.0 * Count(Distinct Case When Tall = 1 Then UserId End) 
       / Count(Distinct UserId)
From   @height