Most winning team |
Win ratio (%) |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND((100*(Won + (Drawn/2))/Matches)) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Most scoring team |
Avg. score |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND((Score/Matches),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Best defensive team |
Avg. opp. score |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND((OppScore/Matches),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col ASC, Matches DESC LIMIT 10 Best goal difference |
Avg. goal diff. |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND((Cast(Score-OppScore AS SIGNED)/Matches),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Most violent team |
Avg. casualties |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND((HOF_TEAM_STATS.Cas/Matches),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Least injured team |
Avg. opp. cas. |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND((HOF_TEAM_STATS.OppCas/Matches),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col ASC, Matches DESC LIMIT 10 Best casualty difference |
Avg. cas. diff. |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND((Cast(HOF_TEAM_STATS.Cas-HOF_TEAM_STATS.OppCas AS SIGNED)/Matches),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Most killed players |
Avg. deaths |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND(SUM(p.Status='DEAD')/Matches,2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Casualty/touchdown rate |
Casualties per TD |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND((HOF_TEAM_STATS.Cas/HOF_TEAM_STATS.Score),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Most entertaining team |
Avg. TD+Cas |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND(((HOF_TEAM_STATS.Cas + HOF_TEAM_STATS.Score)/Matches),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Most passing team |
Avg. completions |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND((SUM(Cmp)/Matches),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Most SPP gaining team |
Avg. SPP |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND((SUM(p.Cmp + p.Td*3 + p.Intr*2 + p.Cas*2 +p.Mvp*5)/Matches), 2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Fastest team |
Avg. MA |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND(AVG(p.Ma),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>2
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Strongest team |
Avg. ST |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND(AVG(p.St),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>2
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Most agile team |
Avg. AG |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND(AVG(p.Ag),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>2
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Most armoured team |
Avg. AV |
Team name |
Race |
Coach |
Matches |
SELECT
ROUND(AVG(p.Av),2) AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>2
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Most valuable team |
Value |
Team name |
Race |
Coach |
Matches |
SELECT
Value_Total AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>2
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Most popular team |
Fan Factor |
Team name |
Race |
Coach |
Matches |
SELECT
t.Fan_Factor AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>0
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Most experienced team |
Total SPP |
Team name |
Race |
Coach |
Matches |
SELECT
SUM_Spp AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>2
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10 Richest team |
Treasury |
Team name |
Race |
Coach |
Matches |
SELECT
Treasury AS Col,
t.Race_Name,
Coach_Id,
t.Name as TName,
Matches,
Fullname,
t.Id as Team_Id,
Small_Logo,
Def_Small_Logo,
Race_Id
FROM
HOF_TEAM_STATS,
TEAM_INFO_VIEW t,
USER,
PLAYER_DETAILS_VIEW p,
PLAYER_TYPE
WHERE
HOF_TEAM_STATS.Team_Id=t.Id
AND Matches>=3
AND t.Coach_Id=USER.Id
AND t.Id=p.Team_Id
AND PLAYER_TYPE.Id=p.Player_Type_Id
AND p.Status>2
GROUP BY p.Team_Id ORDER BY Col DESC, Matches DESC LIMIT 10