<?php
$leagueStandingQuery = "
SELECT 
    team, 
    COUNT(*) AS played, 
    COUNT(CASE WHEN homeScore > awayScore THEN 1 END) AS won, 
    COUNT(CASE WHEN homeScore = awayScore THEN 1 END) AS drawn, 
    COUNT(CASE WHEN awayScore> homeScore THEN 1 END) AS lost, 
    SUM(homeScore) AS shotsFor, 
    SUM(awayScore) AS shotsAgainst, 
    SUM(homeScore) - SUM(awayScore) AS shotsDiff,
    SUM(homeRinkPts) AS rinkPts,
    SUM(
          CASE WHEN homeScore > awayScore THEN 2 ELSE 0 END 
        + CASE WHEN homeScore = awayScore THEN 1 ELSE 0 END
        + homeRinkPts
    ) AS pts 
FROM (
    SELECT homeTeam AS team, homeScore, awayScore, homeRinkPts, awayRinkPts from leagueResults WHERE miniLeagueID = '1' AND ((homeTeam != '') AND (awayTeam != '')) AND ((homeScore >= 0) AND (awayScore >= 0))
  UNION ALL
    SELECT awayTeam AS team, awayScore, homeScore, awayRinkPts, homeRinkPts from leagueResults WHERE miniLeagueID = '1' AND ((homeTeam != '') AND (awayTeam != '')) AND ((homeScore >= 0) AND (awayScore >= 0))
) AS a 
GROUP BY team
ORDER BY pts DESC, shotsDiff DESC;";

///// NOT BOTH ZERO	\\\\\
$query = "
    SELECT homeTeam AS team, homeScore, awayScore from leagueResults WHERE miniLeagueID = '1' AND ((homeTeam != '') AND (awayTeam != '')) AND (homeScore <> 0 + awayScore <> 0)
  UNION ALL
    SELECT awayTeam AS team, awayScore, homeScore from leagueResults WHERE miniLeagueID = '1' AND ((homeTeam != '') AND (awayTeam != '')) AND (homeScore <> 0 + awayScore <> 0)";

///// USING HOME SCORE -1 TO SHOW NOT PLAYED	\\\\\
$query = "
    SELECT homeTeam AS team, homeScore, awayScore from leagueResults WHERE miniLeagueID = '1' AND ((homeTeam != '') AND (awayTeam != '')) AND ((homeScore >= 0) AND (awayScore >= 0))
  UNION ALL
    SELECT awayTeam AS team, awayScore, homeScore from leagueResults WHERE miniLeagueID = '1' AND ((homeTeam != '') AND (awayTeam != '')) AND ((homeScore >= 0) AND (awayScore >= 0))";
?>