Creating stairs in sql

Question

I need help to make the stairs.

Here is the info:

Tables:

cricket_teams(id, name) cricket_matches(id, hometeam, awayteam, format, season, winner) cricket_teamperformance(id, matchid, team, innings, score) 

(there are other fields in the tables that are not needed for this query)

I need to get it so that it gives me wins , draws (where the winner = 0), losses, FIP (when the team scores more points in the first innings), BP (in this case only win) and Points (in bold). I have a request, but I have a few questions. I can’t understand how to get draws and losses, and also not to get teams that did not win the game. Here is what I have:

 SELECT b.team, b.name, COUNT(c.wins) as W, count(b.name)*2 as FIP, COUNT(c.wins)*2 as BP, COUNT(c.wins)*4+(count(b.name)*2)+(COUNT(c.wins)*2) as Pts FROM (SELECT a.name, a.score, a.matchid, a.team FROM (SELECT cricket_teams.name, score, matchid, team FROM `cricket_teamperformance` LEFT JOIN cricket_teams ON cricket_teamperformance.team = cricket_teams.id INNER JOIN cricket_matches ON cricket_teamperformance.matchid = cricket_matches.id WHERE cricket_matches.format=3 AND cricket_teamperformance.innings = 1 AND cricket_matches.season = 1 OR cricket_matches.format=3 AND cricket_teamperformance.innings = 2 AND cricket_matches.season = 1 ORDER BY matchid, score DESC) as a GROUP BY matchid) as b, (SELECT COUNT(winner) as wins, cricket_teams.name FROM cricket_matches LEFT JOIN cricket_teams ON cricket_matches.winner = cricket_teams.id WHERE format = 3 AND season = 1) as c GROUP BY b.name ORDER BY Pts DESC 

And this returns:

 team name W FIP BP Pts 4 Chargers 2 4 4 16 2 Hawks 1 2 2 8 1 Ninjas 1 2 2 8 

It should look like this (P - matches played, D - draws, L - losses):

 team name PWDL FIP BP Pts 4 Chargers 2 2 0 0 4 4 16 2 Hawks 2 1 0 1 2 2 8 1 Ninjas 2 1 0 1 2 2 8 3 Wolves 2 0 0 2 0 0 0 

There is another team, Wolves, who have not won any of their two games. I also need to get these draws and losses. thanks in advance

* This is a multi-year cricket in which teams can have up to two innings per match. SQL Fiddle: http://sqlfiddle.com/#!2/26e41/2

Answer 1

Here's an alternative solution:

 SELECT team, name, count(*) AS p, sum(win) AS w, sum(draw) AS d, sum(loss) AS l, sum(fi)*2 AS fip, sum(win)*2 AS bp, sum(win)*4 + sum(fi)*2 + sum(win)*2 AS pts FROM (SELECT t.id AS team, t.name, m.winner = t.id AS win, m.winner = 0 AS draw, m.winner <> t.id AND m.winner <> 0 as loss, CASE WHEN p1.score > p2.score THEN p1.team = t.id ELSE p2.team = t.id END AS fi FROM cricket_matches m, cricket_teamperformance p1, cricket_teamperformance p2, cricket_teams t WHERE m.id = p1.matchid AND m.id = p2.matchid AND p1.innings = 1 AND p2.innings = 2 AND (t.id = m.hometeam OR t.id = m.awayteam) AND m.season = 1 AND m.format = 3) tallies GROUP BY team ORDER BY pts DESC, name 

Answer 2

I found that the description of the problem and the table layout are very confusing, but maybe this will help you in some way:

 SELECT ct.id AS TeamID, ct.name AS TeamName, COUNT(DISTINCT cm.id) AS MatchesPlayed, COUNT(DISTINCT CASE WHEN cm.winner = ct.id THEN cm.id END) AS Wins, COUNT(DISTINCT CASE WHEN cm.winner = 0 THEN cm.id END) AS Draws, COUNT(DISTINCT CASE WHEN cm.winner != 0 AND cm.winner != ct.id THEN cm.id END) AS Losses, COUNT(DISTINCT CASE WHEN (ctph.innings = 1 OR ctpa.innings = 1) AND (ctph.team = ct.id AND ctph.score > ctpa.score) THEN ctph.matchid WHEN (ctph.innings = 1 OR ctpa.innings = 1) AND (ctpa.team = ct.id AND ctpa.score > ctph.score) THEN ctpa.matchid END) AS FIP FROM cricket_teams AS ct LEFT JOIN cricket_matches AS cm ON cm.hometeam = ct.id OR cm.awayteam = ct.id LEFT JOIN cricket_teamperformance AS ctph ON ctph.team = cm.hometeam AND ctph.matchid = cm.id LEFT JOIN cricket_teamperformance AS ctpa ON ctpa.team = cm.awayteam AND ctpa.matchid = cm.id GROUP BY ct.id, ct.name