Databases
StackOverflow2013
Postsdbo
POMaking a ladder in sql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMaking a ladder in sql
    primarykey
    data
    text
    <p>I need some help making a ladder. </p> <p>Heres the info:</p> <p>Tables:</p> <pre><code>cricket_teams(id, name) cricket_matches(id, hometeam, awayteam, format, season, winner) cricket_teamperformance(id, matchid, team, innings, score) </code></pre> <p>(there are other fields in the tables that are unnecessary for this query)</p> <p>I need to get it so that it gives me the <strong>wins</strong>, draws (where winner = 0), losses, <strong>FIP</strong> (when a team scores more in the first innings), <strong>BP</strong> (in this case just winning) and <strong>Points</strong> (Ones in bold are done). I have a query but I have a few issues. I can't figure out how to get the draws and losses, and it also doesn't get teams who haven't won a game. Here's what I've got:</p> <pre><code>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 </code></pre> <p>And that returns this:</p> <pre><code>team name W FIP BP Pts 4 Chargers 2 4 4 16 2 Hawks 1 2 2 8 1 Ninjas 1 2 2 8 </code></pre> <p>It needs to look like this (P is matches played, D is draws, L is losses):</p> <pre><code>team name P W D L 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 </code></pre> <p>There is another team, Wolves, who have not won either of their two games. I also need to retrieve those draws and losses. Thanks in advance</p> <p>*This is multiday cricket, meaning teams can have up to two innings per match. SQL Fiddle: <a href="http://sqlfiddle.com/#!2/26e41/2" rel="nofollow">http://sqlfiddle.com/#!2/26e41/2</a></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScameronjonesweb
    1. UScameronjonesweb
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMaking a ladder in sql
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMaking a ladder in sql
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMaking a ladder in sql
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODo yo have a SQL Fiddle for this?
      singulars
      1. POMaking a ladder in sql
      1. USRGV
    2. CONo, never heard of it
      singulars
      1. POMaking a ladder in sql
      1. UScameronjonesweb
    3. COCan you explain the logic for BP?
      singulars
      1. POMaking a ladder in sql
      1. USRGV
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload