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.

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.

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

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

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.

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

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.

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.

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

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;

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]

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.

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