Welcome :Guest

Congratulations!!!

Top 5 Contributors of the Month
Melody Anderson

 Home >> Code Snippets >> SQL Query >> Post New Resource

# Calculating probabilities for Tunisia football championship before 3 rounds

Posted By:Kemal AL GAZZAH       Posted Date: May 27, 2016    Points: 25    Category: SQL Query    URL: http://www.dotnetspark.com

sql script, stored procedure, probability, football, ESS, CSS, EST, Etoile sportive du Sahel

Tunisia Football championship will end after 3 rounds.
The actual ranking, before the last 3 rounds is as follows.

ESS (Etoile sportive du Sahel) 70 points
CSS (Club Sportif Sfaxien) 66 points
EST (EspĂ©rance sportive de Tunis) 65 points

I was thinking about calculating the probability for ESS to win the championship
so I made the following sql script which calculates the occurrence of each result knowing that
when a team wins a match it earns 3 points
when the match is Null, it earns 1 point
when it loses, no point earned

1) First I created a table as follows
declare @mytab table (id int identity(1,1),team varchar(1), play int,res int)

-[team] will will have the following values (A for ESS, B for CSS and C for EST)
-[play] will have the following values (1,2,3) the remaining rounds to be played
-[res] will have one of the 3 possible values, points earned (0,1,3)

2)Filling the table
declare @play as int
set @play=1
while @play < 4
begin
insert into @mytab(team,play,res) values('A',@play,0)
insert into @mytab(team,play,res) values('A',@play,1)
insert into @mytab(team,play,res) values('A',@play,3)

insert into @mytab(team,play,res) values('B',@play,0)
insert into @mytab(team,play,res) values('B',@play,1)
insert into @mytab(team,play,res) values('B',@play,3)

insert into @mytab(team,play,res) values('C',@play,0)
insert into @mytab(team,play,res) values('C',@play,1)
insert into @mytab(team,play,res) values('C',@play,3)

set @play=@play+1
end

the table will have 9 rows after this script

3) Calculating the total number of possible results.

declare @s as int
select @s=count(1)   from(

select A+@a a,B+@b b,C+@c c,count(1) N from(
select t1.a+t2.a+t3.a a,t1.b+t2.b+t3.b b,t1.c+t2.c+t3.c c from(
select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t1
inner join
(select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t2 on t1.play=1 and t2.play=2
inner join
(select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t3 on t3.play=3) tt  group by a,b,c)  TTT

4) Then here the probability of each result

select win,COUNT(1) N,convert(decimal(4,2),100.0*COUNT(1)/@s)  "P%" from(
select case
when a >b and a >c then 'A'
when B >A and B >c then 'B'
when C >b and C >A then 'C'
when a=b and a > c then 'AB'
when a=c and a > b then 'AC'
when b=c and b > a then 'BC'
when A=b and a=c then 'ABC'
end win
from(
select A+@a a,B+@b b,C+@c c,count(1) N from(
select t1.a+t2.a+t3.a a,t1.b+t2.b+t3.b b,t1.c+t2.c+t3.c c from(
select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t1
inner join
(select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t2 on t1.play=1 and t2.play=2
inner join
(select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t3 on t3.play=3) tt  group by a,b,c) tb) TTT GROUP BY WIN order by len(win),win

below you have the complete stored procedure

execution
exec usp_probability 70,66,65

Result
ESS 588 80.66
CSS 92 12.62
EST 49 6.72

So Finally ETOILE has 80.66% of Chance to Win the Tunisian Championship before the last 3 rounds !

Create proc usp_probability(@a int,@b int,@c int)
as
begin
set nocount  on
declare @mytab table (id int identity(1,1),team varchar(1), play int,res int)

declare @play as int
set @play=1
while @play < 4
begin
insert into @mytab(team,play,res) values('A',@play,0)
insert into @mytab(team,play,res) values('A',@play,1)
insert into @mytab(team,play,res) values('A',@play,3)

insert into @mytab(team,play,res) values('B',@play,0)
insert into @mytab(team,play,res) values('B',@play,1)
insert into @mytab(team,play,res) values('B',@play,3)

insert into @mytab(team,play,res) values('C',@play,0)
insert into @mytab(team,play,res) values('C',@play,1)
insert into @mytab(team,play,res) values('C',@play,3)

set @play=@play+1
end

set nocount off
declare @s as int
select @s=count(1)   from(

select A+@a a,B+@b b,C+@c c,count(1) N from(
select t1.a+t2.a+t3.a a,t1.b+t2.b+t3.b b,t1.c+t2.c+t3.c c from(
select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t1
inner join
(select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t2 on t1.play=1 and t2.play=2
inner join
(select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t3 on t3.play=3) tt  group by a,b,c)  TTT

select "who win" "CHAMPION",SUM(N) N,SUM("P%") "P%"
from(
select case
when win='A' then 'ESS'
when win='B' then 'CSS'
when win='C' then 'EST'
when win like 'A%' then 'ESS'
when win like 'B%' then 'CSS' end "who win",COUNT(1) N,convert(decimal(4,2),100.0*COUNT(1)/@s)  "P%" from(
select case
when a >b and a >c then 'A'
when B >A and B >c then 'B'
when C >b and C >A then 'C'
when a=b and a > c then 'AB'
when a=c and a > b then 'AC'
when b=c and b > a then 'BC'
when A=b and a=c then 'ABC'
end win
from(
select A+@a a,B+@b b,C+@c c,count(1) N from(
select t1.a+t2.a+t3.a a,t1.b+t2.b+t3.b b,t1.c+t2.c+t3.c c from(
select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t1
inner join
(select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t2 on t1.play=1 and t2.play=2
inner join
(select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t3 on t3.play=3) tt  group by a,b,c) tb) TTT GROUP BY WIN ) TaT group by "who win" order by N desc
end

Responses
Author: Kemal AL GAZZAH         Company URL: http://www.dotnetspark.com
Posted Date: May 30, 2016

After the matches of 29/05/2016, he after the stored procedure update for 2 remaining rounds

alter proc usp_probability_2rounds(@a int,@b int,@c int)
as
begin
set nocount on
declare @mytab table (id int identity(1,1),team varchar(1), play int,res int)

declare @play as int
set @play=1
while @play < 3
begin
insert into @mytab(team,play,res) values('A',@play,0)
insert into @mytab(team,play,res) values('A',@play,1)
insert into @mytab(team,play,res) values('A',@play,3)

insert into @mytab(team,play,res) values('B',@play,0)
insert into @mytab(team,play,res) values('B',@play,1)
insert into @mytab(team,play,res) values('B',@play,3)

insert into @mytab(team,play,res) values('C',@play,0)
insert into @mytab(team,play,res) values('C',@play,1)
insert into @mytab(team,play,res) values('C',@play,3)

set @play=@play+1
end

set nocount off
declare @s as int
select @s=count(1) from(

select A+@a a,B+@b b,C+@c c,count(1) N from(
select t1.a+t2.a a,t1.b+t2.b b,t1.c+t2.c c from(
select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t1
inner join
(select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t2 on t1.play=1 and t2.play=2
) tt group by a,b,c) TTT

select "who win" "CHAMPION",SUM(N) N,SUM("P%") "P%"
from(
select case
when win='A' then 'ESS'
when win='B' then 'CSS'
when win='C' then 'EST'
--when win='Ab' or win='AC' or win='ABC' then 'ESS'
--when win='BC' then 'CSS'
else win
--when win = 'AB' then 'ESS'
--when win like 'B%' then 'CSS'

end "who win",COUNT(1) N,convert(decimal(4,2),100.0*COUNT(1)/@s) "P%" from(
select case
when a >b and a >c then 'A'
when B >A and B >c then 'B'
when C >b and C >A then 'C'
when a=b and a > c then 'AB'
when a=c and a > b then 'AC'
when b=c and b > a then 'BC'
when A=b and a=c then 'ABC'
end win
from(
select A+@a a,B+@b b,C+@c c,count(1) N from(
select t1.a+t2.a a,t1.b+t2.b b,t1.c+t2.c c from(
select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t1
inner join
(select t1.play,t1.res a,t2.res b,t3.res c from @mytab t1
inner join @mytab t2 on t1.play=t2.play and t1.team='A' and t2.team='B'
inner join @mytab t3 on t1.play=t3.play and t1.team='A' and t3.team='C') t2 on t1.play=1 and t2.play=2
) tt group by a,b,c) tb) TTT GROUP BY WIN ) TaT group by "who win" order by N desc
end

Post Comment