sql - Statistical significance for average values? -

consider voting system. e.g. cars.

• 10 people think give car a score of 70%.
• 1000 people think give car a score of 60%.

hence, have values 0.7 , 0.6. how compare these values? goes without saying 1000 votes more significant 10 votes. preferably, efficiently in sql (using avg function or similar).

there ought well-known formula type of problem. please help!

ok, let's count people. have 1010 people 1000 gave score 60 , 10 gave score 70.

average score is:

(1000 * 60 + 10 * 70)/(1000 + 10) = 60,09 

now put them table , run query against it:

create table scores (cust_id int identity(1,1), car char(1), score float); go  ------------------------------------------------------ declare @i int = 0;  while @i < 1000  begin     insert scores(car, score) values ('a', 60.0);     set @i = @i + 1 end  while @i < 1010 begin     insert scores(car, score) values ('a', 70.0);     set @i = @i + 1 end;  ------------------------------------------------------ select car, avg(score) [score], count(cust_id) [people_count] scores group car 

result:

car score   people_count ------------------------   60,09   1010 

sqlfiddle

---

update

create function compare_scores (@n1 int, @sc1 float, @n2 int, @sc2 float) returns varchar(10) begin     return case when (@n1 * @sc1) <= (@n2 * @sc2) (case when (@n1 * @sc1) = (@n2 * @sc2) 'equal' else 'less' end) else 'greater' end end  ---------------------------------------------------------- select dbo.compare_scores(10, 10.0, 1000, 8.0) [result] union select dbo.compare_scores(10, 10.0, 10, 10.0) [result] union select dbo.compare_scores(1000, 10.0, 10, 8.0) [result] 

result:

result ------- less equal greater