hello Nocton,

i've had a go at this & come up with the following:-

i found it best to create two queries. the field names used throughout are as provided by yourself so fingers crossed it works with your data.

Query 1. select & calculate the required values from your tables.

my thoughts are that we have one glaring omission, each year will bring a new season. as this is not part of the remit it is not accounted for.

this is a series of sub-queries. each process is duplicated home tie/away tie.

copy this code, paste it into a query window, select 'Run SQL command directly' & execute.

if success is achieved then create a view from this code & name it as 'vLeagueTable'.

- Code: Select all Expand viewCollapse view
`select C."ClubID", C."ClubName",`

coalesce(H_WINS.H_WIN, 0) + coalesce(A_WINS.A_WIN, 0) "TotalMatchWins",

coalesce(H_STATS.G_WON, 0) + coalesce(A_STATS.G_WON, 0) "GameWins",

coalesce(H_STATS.HP_QTY, 0) + coalesce(A_STATS.AP_QTY, 0) "HoopPoints",

coalesce(H_STATS.G_PLAYED, 0) + coalesce(A_STATS.G_PLAYED, 0) "GamesPlayed"

from

"Clubs" C

left join --total home game points & total home game wins & total home games played

(

select

M."HomeClubID",

sum(G."HomePoints") HP_QTY,

count(*) G_PLAYED,

sum(case when G."HomePoints" > G."AwayPoints" then 1 end) G_WON

from

"Matches" M

join

"GameResults" G

on G."MatchID" = M."MatchID"

group by M."HomeClubID"

) H_STATS

on H_STATS."HomeClubID" = C."ClubID"

left join --total away game points & total away game wins & total away games played

(

select

M."AwayClubID",

sum(G."AwayPoints") AP_QTY,

count(*) G_PLAYED,

sum(case when G."HomePoints" < G."AwayPoints" then 1 end) G_WON

from

"Matches" M

join

"GameResults" G

on G."MatchID" = M."MatchID"

group by M."AwayClubID"

) A_STATS

on A_STATS."AwayClubID" = C."ClubID"

left join --total home match wins

(

select M."HomeClubID", sum(case when G.HP > G.AP then 1 end) H_WIN

from

(select "MatchID", "HomeClubID" from "Matches") M

join

(select "MatchID", sum("HomePoints") HP, sum("AwayPoints") AP from "GameResults" group by "MatchID") G

on G."MatchID" = M."MatchID"

group by "HomeClubID"

) H_WINS

on H_WINS."HomeClubID" = C."ClubID"

left join --total away match wins

(

select M."AwayClubID", sum(case when G.HP < G.AP then 1 end) A_WIN

from

(select "MatchID", "AwayClubID" from "Matches") M

join

(select "MatchID", sum("AwayPoints") AP, sum("HomePoints") HP from "GameResults" group by "MatchID") G

on G."MatchID" = M."MatchID"

group by "AwayClubID"

) A_WINS

on A_WINS."AwayClubID" = C."ClubID"

Query 2. present the data according to remit.

if more than two clubs have won the same number of matches & the number of matches won = max("TotalMatchWins") then this query will fail because the embedded query can only produce a single value.

i think this is not a sensible way to choose a winner because the formula fails in the case of a three or more way tie.

'GamesPlayed' = the total number of games played. if you want matches played then just divide 'GamesPlayed' by the number of games in a match ('GamesPlayed'/12).

the sum of the away points is deducted from the sum of the home points when:-

the total number of matches won by a home club = the total number of matches won by an away club & the home club & away club are not the same club & the number of matches won = max("TotalMatchWins")

this produces either a positive or negative value which can be used to sort the data.

create the query from this code & execute.

- Code: Select all Expand viewCollapse view
`--works when 2 clubs win the same number of matches & the number of matches won = max(matches won)`

--fails when more than 2 clubs win the same number of matches & the number of matches won = max(matches won)

select "ClubName", "TotalMatchWins", "GameWins", "HoopPoints", "GamesPlayed"--, "TieResult"

from

(

select Q.*,

(

select sum(G."HomePoints") - sum(G."AwayPoints")

from "Matches" M

join "GameResults" G

on G."MatchID" = M."MatchID"

where M."HomeClubID" = Q."ClubID"

and

M."AwayClubID" = (select "ClubID" from "vLeagueTable" where "ClubID" <> Q."ClubID" and "TotalMatchWins" = Q."TotalMatchWins" and "TotalMatchWins" = (select max("TotalMatchWins") from "vLeagueTable"))

)

"TieResult"

from "vLeagueTable" Q

)

order by "TotalMatchWins" desc, coalesce("TieResult", 0) desc, "GameWins" desc, "HoopPoints" desc

open office 4.1.7 & LibreOffice 6.3.5.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10