YEAR(_when) = 2024
WITH inside AS (SELECT *,
_for AS '_for2', _agt AS '_agt2', (_for) - (_agt) AS '_mgn2',
IF((_for) - (_agt) > 0, 'W', IF((_for) - (_agt) = 0, 'D', 'L')) AS '_WL2',
1 AS '_pre'
FROM haven), middle AS (SELECT *, ROW_NUMBER() OVER (ORDER BY _when DESC) as '_rank' FROM inside WHERE YEAR(_when) = 2024) SELECT GROUP_CONCAT(CONCAT_WS('#', YEAR(_when), _RND(_rnd), _tm, _op, CONCAT(_HA, '@', _vn), _WL2, ABS(_mgn2), _cwd) ORDER BY _when DESC SEPARATOR '|' LIMIT 25) AS 'data{#}' , _tm AS 'Team{!Team!}', count(*)AS 'P{!Played!}', SUM(IF(_WL2 = 'W', 1, 0)) AS 'W{!Games Won!}', SUM(IF(_WL2 = 'L', 1, 0)) AS 'L{!Games Lost!}', SUM(IF(_WL2 = 'D', 1, 0)) AS 'D{!Games Drawn!}', AVG(_for2) AS 'PF{,0.1!Average Points For!}', AVG(_agt2) AS 'PA{,0.1!Average Points Against!}', SUM(IF(_WL2 = 'W', 4, IF(_WL2 = 'D', 2, 0))) AS 'PP{!Premiership Pts!}', 100 * SUM(_for2) / SUM(_agt2) AS '%{0.1%!Percentage!}', AVG(_opFin) AS 'OF{0.2!Average Opponent Finish!}', REVERSE(GROUP_CONCAT(_WL ORDER BY _when DESC SEPARATOR '-' LIMIT 5)) AS 'L5{!Last 5 W/L!}' FROM middle WHERE 1 GROUP BY _tm ORDER BY SUM(IF(_for2 > _agt2, 40, IF(_for2 = _agt2, 20, 0))) + (SUM(_for2) / SUM(_agt2)) DESC
Cheat Sheet;
Who; Blu Bom Bul Cat Cro Dem Doc Eag Gia Haw Kan Lio Mag Pow Sai Sun Swa Tig
Who (shortcuts); All
ISInterstate teams (from outside VIC) NSW
OSOverseas (China / NZ) QLD SA VIC WA
What selectors; Haw/Opponent @MCG/Venue >100k/Crowd Size 1st/Ladder Position 6d/Days Break 17:30/Game Start time Mon:Day or Month (note semi-colon) W/Only Wins L/Only Losses D/Draws H/Home Games A/Away Games HS/Home State AS/Away (same) State HHS/Home games played in Home State AHS/Away games played in Home State IS/Interstate Games HIS/Home games played Interstate AIS/Away games played Interstate)
What values; TTeams PGames Played WWins LLosses DDraws PFPoints For PAPoints Against PPPremiership Points %Percentage W%Win Ratio WRWin Ratio (draws counted as 0.5) TFTeam Ladder Finish OFOpponent Ladder Finish CCrowd YYear TBTeam Break OBOpponent Break GSGames Since DSDays Since YSYears Since SW/L Streak L5W/L for last 'n' games WLDWin-Loss-Draw
What modifiers; nilDefault (usually average) =Total Sum +Maximum -Minimum
Where; AdeAdelaide Oval BelBellerive Oval (TAS) BlaBlacktown Oval (NSW) CarCarrara (Gold Coast) CazCazalys Stadium (QLD/Cairns) DocDocklands Stadium EurEureka Stadium (VIC/Ballarat) FooFooty Park (SA) GabThe GABBA (QLD) JiaJiangth Stadium (China) KarKardinia Park (Geelong) MCGMCG ManManuka Oval (ACT) MarMarrara Oval (Darwin) MtBMount Barker (SA) NorNorwood Oval (SA) PerPerth Stadium (WA) PriPrinces Park RivRiverway Stadium (Townsville QLD) SCGSCG StaStadium Australia (NSW) SubSubiaco (WA) SydSydney Showgrounds TraTraeger Park (Alice Springs/NT) WACThe WACA (WA) WelWellington (NZ) YorYork Park (Launceston/TAS)
Where (shortcuts); All ACT ISInterstate teams (from outside VIC) NSW NT OSOverseas (China / NZ) QLD SA TAS VIC WA
When; 2024Year;
eg. 2024
eg. 2022-2024
eg. >=2022 17:30Game Start time
eg. 17:30
eg. 15:50-18:30
eg. >18:30 R5Rounds
eg. R5
eg. R1-5
eg. R>=5 R30Qualifying Final R31Elimination Final R32Semi Final R33Preliminary Final R34Grand Final R35Grand Final Replay MonDay of the Week JulMonth of the Year
Why; All/All games 8T/8 games total (any team vs any opponent) 8E/8 games each team (vs any opponent) 8V/8 games for each team vs each opponent 8U/8 home games and 8 away games for each team vs each opponent /D-By date backwards /D+By date forwards /M-By margin backwards /M+By margin forwards /C-By crowd backwards /C+By crowd forwards Q1234Include scores from all four quarters
Why -vs-; 1-8thLadder Position
eg. 8th
eg. 1-8th
eg. >8th 7-8dDays Break
eg. 7d
eg. 7-8d
eg. <7d <15kCrowd Size
eg. 50k
eg. 25-50k
eg. <10k >18:30Game Start
eg. 14:30
eg. 15:30-16:30
eg. >=17:30 WWin LLose DDraw HPlaying at Home APlaying Away HSPlaying in Home State ISPlaying out of State (Haw)Team played in the previous round (@Per)Venue played at in the previous round
