SELECT FixtCompetition, CompName, TeamName, SUM(CleanSheet) as CleanSheets, SUM(goals) as TotalGoals, Count(*) as TotApp
FROM
(
SELECT b.FixtMatchID,
CASE WHEN ((b.FixtMatchHomeGlsET IS NULL) AND (b.FixtMatchAwayGoals = 0)) THEN 1
WHEN (b.FixtMatchAwayGlsET = 0) THEN 1
ELSE 0 END as CleanSheet,
c.TeamName, d.CompName, b.FixtCompetition,
CASE WHEN PS.goalscored IS NULL THEN 0
ELSE PS.goalscored END as goals
FROM `nwcl_lineups` as a
INNER JOIN `nwcl_fixtures` as b
ON a.MatchID = b.FixtMatchID
LEFT JOIN `nwcl_teams` as c
ON b.FixtMatchHomeID = c.TeamID
LEFT JOIN `nwcl_competitions` as d
ON b.FixtCompetition = d.compid
LEFT JOIN (
SELECT team, matchid, SUM(goals) as goalscored
FROM nwcl_playerstats
WHERE player = '10256'
GROUP BY team, matchid
) as PS
ON b.FixtMatchHomeID = PS.team
AND b.FixtMatchID = PS.matchid
WHERE b.FixtMatchDate < '2025-05-15 17:49:13'
AND (`homeplayer1` LIKE '10256' OR `homeplayer2` LIKE '10256' OR `homeplayer3` LIKE '10256' OR `homeplayer4` LIKE '10256' OR `homeplayer5` LIKE '10256' OR `homeplayer6` LIKE '10256' OR `homeplayer7` LIKE '10256' OR `homeplayer8` LIKE '10256' OR `homeplayer9` LIKE '10256' OR `homeplayer10` LIKE '10256' OR `homeplayer11` LIKE '10256' OR `homesub1played` LIKE '10256' OR `homesub2played` LIKE '10256' OR `homesub3played` LIKE '10256' OR `homesub4played` LIKE '10256' OR `homesub5played` LIKE '10256')
AND FixtAban IS NULL
AND FixtVoid IS NULL
AND FixtPosp IS NULL
UNION ALL
SELECT b.FixtMatchID,
CASE WHEN ((b.FixtMatchAwayGlsET IS NULL) AND (b.FixtMatchHomeGoals = 0)) THEN 1
WHEN (b.FixtMatchHomeGlsET = 0) THEN 1
ELSE 0 END as CleanSheet,
c.TeamName, d.CompName, b.FixtCompetition,
CASE WHEN PS.goalscored IS NULL THEN 0
ELSE PS.goalscored END as goals
FROM `nwcl_lineups` as a
INNER JOIN `nwcl_fixtures` as b
ON a.MatchID = b.FixtMatchID
LEFT JOIN `nwcl_teams` as c
ON b.FixtMatchAwayID = c.TeamID
LEFT JOIN `nwcl_competitions` as d
ON b.FixtCompetition = d.compid
LEFT JOIN (
SELECT team, matchid, SUM(goals) as goalscored
FROM nwcl_playerstats
WHERE player = '10256'
GROUP BY team, matchid
) as PS
ON b.FixtMatchHomeID = PS.team
AND b.FixtMatchID = PS.matchid
WHERE b.FixtMatchDate < '2025-05-15 17:49:13'
AND (`awayplayer1` LIKE '10256' OR `awayplayer2` LIKE '10256' OR `awayplayer3` LIKE '10256' OR `awayplayer4` LIKE '10256' OR `awayplayer5` LIKE '10256' OR `awayplayer6` LIKE '10256' OR `awayplayer7` LIKE '10256' OR `awayplayer8` LIKE '10256' OR `awayplayer9` LIKE '10256' OR `awayplayer10` LIKE '10256' OR `awayplayer11` LIKE '10256' OR `awaysub1played` LIKE '10256' OR `awaysub2played` LIKE '10256' OR `awaysub3played` LIKE '10256' OR `awaysub4played` LIKE '10256' OR `awaysub5played` LIKE '10256')
AND FixtVoid IS NULL
AND FixtAban IS NULL
AND FixtPosp IS NULL
) as dertable
GROUP BY FixtCompetition, CompName, TeamName
ORDER BY FixtCompetition
| The Emirates F.A Cup | Abbey Hey | 1 | 0 | 0 |
| The Isuzu FA Vase | Abbey Hey | 3 | 0 | 0 |
| The Macron Cup | Abbey Hey | 1 | 0 | 0 |
| Premier Division | Abbey Hey | 21 | 0 | 4 |
| Manchester Premier Cup | Abbey Hey | 2 | 0 | 0 |
| First Division South Play Offs | Sandbach United | 1 | 0 | 0 |
