SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Calculate Winning Percentage With Single mySql Query

    I have a mySQL database for a pool league and I want to calculate the winning percentage when I SUM the totals after each week. How do I write a statement that will do this? Below is my table and the statement I am using to SUM but it don't calculate the percentage when I add weekly stats.


    team | wins | losses | pct
    hueys | 5 | 5 | .500
    buds | 2 | 8 | .200
    fudas | 4 | 6 | .400
    ---------------------------------------------------
    SELECT DISTINCT Team, SUM( Wins ) as Wins, SUM( Losses ) as Losses, Pct
    FROM standings
    GROUP BY Team
    ORDER BY Wins
    DESC LIMIT 0 , 500

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
      Team,
      SUM(Wins) AS Wins,
      SUM(Losses) AS Losses,
      SUM(Wins) / ((SUM(Wins) + SUM(Losses)) AS Pct
    ...
    Does that work?

    You can't mix group and non-group columns in a GROUP BY query without nondeterministic results -- it doesn't know what row you want the Pct from when combining all the rows for each team into one.

  3. #3
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Thanks so much, I should have noticed that. Code below is working like a charm! I added the "3" so the pct would result in 3 decimals. My next obstacle will be to add a column for GB (games behind). Any thoughts??


    SELECT DISTINCT TEAM,
    SUM(wins) as W,
    SUM(losses) as L,
    TRUNCATE (SUM(wins)/((SUM(wins)+SUM(losses))),3) as PCT
    FROM standings
    GROUP BY team
    ORDER BY W DESC

  4. #4
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't figure out how to add a statement for calculating GB (games behind). Any ideas out there?? This is what I have so far and it works perfect, but I need to add the games behind statement, I am using mySQL 5.0 and the 'IF' statement isn't doing it for me.



    SELECT DISTINCT TEAM,
    SUM(wins) as W,
    SUM(losses) as L,
    TRUNCATE (SUM(wins)/((SUM(wins)+SUM(losses))),3) as PCT
    FROM standings
    GROUP BY team
    ORDER BY W DESC

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What does "games behind" mean?

    And you don't need DISTINCT in the query, it is not possible for the rows to not be distinct when you are grouping by one column.

  6. #6
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If Team A has 5 wins 0 losses and Team B has 4 wins and 1 losses, then Team B would be 1/2 (0.5) games behind the first place team (Team A).
    The phrase games behind or games back (often abbreviated as GB in tables), is a common way to reflect the gap between a leading team and another team in a sports league, conference, or division. It is determined by adding the absolute difference in wins between a leading team and the team of interest to the absolute difference in losses between the two teams, and dividing by two. The leading team itself is always zero games behind. Usually this is indicated in tables by a dash rather than a zero.

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Try

    Code:
    SELECT 
      team,
      SUM(wins) as W,
      SUM(losses) as L,
      TRUNCATE (SUM(wins)/((SUM(wins)+SUM(losses))),3) as PCT,
      (SELECT MAX(wins) FROM standings) - SUM(wins) AS `behind`
    FROM standings
    GROUP BY team
    ORDER BY W DESC

  8. #8
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't have a program. I made a web form to enter the "Team", "Wins", "Losses" online then submit to database. I use the query to calculate the winning percentage (PCT) and would also like the query to calculate the games behind (GB).

    I don't know php or I would write a program relating it to the database and have the admin (me) enter/post everything online automatically so teams can login (as users) and view the standings & schedule etc.

  9. #9
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It brought up the behind column but the values are not right.

    Sorry for the bad formatting , I fixed it here..


    team .................... W.....L.....PCT...behind
    Cedar Creek 2 ...........24.....8....0.750....-15
    Cedar Creek 1 ...........21....11....0.656....-12
    Champs ..................18....14....0.562.....-9
    Fondas ..................15....17....0.468.....-6
    Sportsmans 2 ............13....19....0.406.....-4
    Hueys ...................12....20....0.375.....-3
    Sportsmans 1 ............10....22....0.312.....-1
    Bristol Ridge Expansion ..9....23....0.281......0
    Bristol Ridge 1 ..........6....26....0.187......3
    Bristol Ridge Maynards ...3....29....0.093......6


    As you can see, Cedars Creek 2 is in first place, their 'behind' should be 0 or a dash (-)
    Last edited by tomgibson; Oct 8, 2009 at 21:53. Reason: poor formatting

  10. #10
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Oh, right, the grouping, sorry. I have a headache, so at this point I'd just write it in the program instead of the query. One loop over the results identifies the row with the max wins, then when you display the rows, you can subtract from that number to show the "behind" number.

  11. #11
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    So you want the GB for every team? If there are 10 teams each of those ten teams should have 9 GB calculations in comparison to the other teams?

  12. #12
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    I'm not even half sure this is close to what you would like.

    Code SQL:
    SELECT
        s.Team,
        SUM(s.Wins) AS total_wins,
        SUM(s.Losses) AS total_losses,
        s2.Team AS comp_team,
        ((SUM(s.Wins) - s2.Wins) + ((SUM(s.Losses) - s2.Losses)/2)) AS GB
    FROM 
        standings s
        ,(
         SELECT
              Team,
              SUM(Wins) AS Wins,
              SUM(Losses) AS Losses
           FROM 
              standings
          GROUP 
             BY 
              Team) s2
    WHERE
        s.Team <> s2.Team
    GROUP 
       BY 
        s.Team
    ORDER
       BY 
        total_wins DESC
    LIMIT 
        0 , 500

  13. #13
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope.. That don't work in mySQL query. error from the 's' syntax. What is the "s." for anyway?

  14. #14
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    So you want the GB for every team? If there are 10 teams each of those ten teams should have 9 GB calculations in comparison to the other teams?
    The other 9 teams will be compared for the GB from the top team in the standings. The top team will show a "0" or a "dash" because they would not be any games behind any other team obviously. The code above functions but it brings the wrong values to GB column. Here is a piece of code that says it's supposed to work but it's for an older version of mySQL, I think 4.0

    SELECT team, wins AS W, losses AS L,
    TRUNCATE(wins/(wins+losses),3) AS PCT,
    IF((@wl_diff - (wins-losses)) = 0,'-',(@wl_diff - (wins-losses))/2) AS GB
    FROM standings1
    ORDER BY wins-losses DESC, PCT DESC;

  15. #15
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Presumably wherever you got that defined @wl_diff in a previous query

  16. #16
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's getting closer!! The code here creates what I want but with one problem; the GB (games behind) produces NULL for every row. What is wrong??


    SELECT DISTINCT TEAM,
    SUM(wins) AS W,
    SUM(losses) AS L,
    TRUNCATE (SUM(wins)/((SUM(wins)+SUM(losses))),3) AS PCT,
    IF (@wl_diff - (wins)-(losses) = 0,'-',(@wl_diff - (wins)-(losses)))/2 AS GB
    FROM standings
    GROUP BY TEAM
    ORDER BY SUM(wins)-SUM(losses) DESC, PCT DESC
    LIMIT 0,500
    -----------------------------------------------------------------------------------
    TEAM......................W....L....PCT.....GB
    Cedar Creek 2............24....8...0.750...NULL
    Cedar Creek 1............21...11...0.656...NULL
    Champs...................18...14...0.562...NULL
    Fondas...................15...17...0.468...NULL
    Sportsmans 2.............13...19...0.406...NULL
    Hueys....................12...20...0.375...NULL
    Sportsmans 1.............10...22...0.312...NULL
    Bristol Ridge Expansion...9...23...0.281...NULL
    Bristol Ridge 1...........6...26...0.187...NULL
    Bristol Ridge Maynards....3...29...0.093...NULL

  17. #17
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    What does "games behind" mean?

    And you don't need DISTINCT in the query, it is not possible for the rows to not be distinct when you are grouping by one column.
    The scores are entered each week into a form on our website which posts the results to the database. So each team is listed in the database for every week the form is submitted. With 20 weeks of play, each Team and their Wins & Losses will be in the database 20 times. That's the reason for the DISTINCT on the Teams and the SUM's on the Wins & Losses.

  18. #18
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tomgibson View Post
    That's the reason for the DISTINCT on the Teams
    As I said above, DISTINCT is not needed there. You are already GROUP BY teams, which means to collapse all the rows for each value of teams into one.

    Adding DISTINCT does nothing, as the GROUP BY that column guarantees distinct rows.

  19. #19
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    As I said above, DISTINCT is not needed there. You are already GROUP BY teams, which means to collapse all the rows for each value of teams into one.

    Adding DISTINCT does nothing, as the GROUP BY that column guarantees distinct rows.
    Sorry I was confused... You are right I don't need DISTINCT. But I just can't - for the life of me - figure out why the GB column is returning NULL for all the rows!!!!!!!! It's driving me CRAZY

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by tomgibson View Post
    But I just can't - for the life of me - figure out why the GB column is returning NULL for all the rows!!!!!!!! It's driving me CRAZY
    it's probably that wacky variable which you neglected to define or initialize
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •