Calculate Winning Percentage With Single mySql Query

Databases
1

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.

[FONT=“Courier New”]
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
[/FONT]

2 
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

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

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

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

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

Try

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

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

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 (-)

10

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

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

I’m not even half sure this is close to what you would like.


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

Nope… That don’t work in mySQL query. error from the ‘s’ syntax. What is the “s.” for anyway?

14

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

Presumably wherever you got that defined @wl_diff in a previous query

16

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??

[FONT=“Courier New”]
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
[/FONT]

17

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

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

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

it’s probably that wacky variable which you neglected to define or initialize