Help SQL question (Calculation)

  • Can someone please help. I am trying to calculate games behind but the query must be missing something (.5 games not showing up) (since not all teams always have same game count top team has played 2 games so teams that played 1 are not really 1 back they should be .5) similar

    Current Results

    Team GP Wins Losses Points PCT GB

    A 2 2 0 4 1.000 0.0

    B 1 1 0 2 1.000 0.0-Should be 0.5 not 0 (halfback)

    C 1 1 0 2 1.000 0.0-Should be 0.5 not 0 (halfback)

    D 1 1 0 2 1.000 0.0-Should be 0.5 not 0 (halfback)

    E 1 0 1 1 0.000 1.0

    F 1 0 1 1 0.000 1.0

    G 1 0 1 1 0.000 1.0

    H 2 0 2 2 0.000 2.0

    My Code

    SELECT

    Team,

    COUNT(*) AS GP,

    SUM(is_win) AS Wins,

    SUM(NOT is_win) AS Losses,

    2 * SUM(is_win) + SUM(NOT is_win) AS Points,

    truncate(SUM(is_win) / ((SUM(is_win)+(sum(NOT is_win)))), 3) AS PCT,

    truncate((case when Count(*) - (sum(is_win) - sum(NOT is_win)) = 0 then 0

    else (abs(Count(*) - (sum(is_win) - sum(NOT is_win))) / 2) end),1)as GB

    FROM

    (

    SELECT

    HomeTeam AS Team,

    HomeScore > AwayScore AS is_win

    FROM Games

    UNION ALL

    SELECT

    AwayTeam AS Team,

    HomeScore < AwayScore AS is_win

    FROM Games

    ) T1

    GROUP BY Team

    ORDER BY Wins DESC, PCT DESC, Losses ASC

  • You seem to be working on something different to SQL Server. I had to translate the code to test the code.

    Let's review the rules.

    For each win, the team gets 2 points and for each lose it gets 1. At least, that's what's happening.

    Ties will be considered as no_wins. As a result, COUNT(*) = (SUM(is_win) - SUM(NOT is_win)).

    With those rules, halfbacks won't be shown as abs(Count(*) - (sum(is_win) - sum(ABS( is_win - 1)))) will always be 0.

    What happens if a team has played 3 games and others have played just 1?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 1. You seem to be working on something different to SQL Server. I had to translate the code to test the code. (I am sql from phpMyAdmin a web database)

    Let's review the rules.

    For each win, the team gets 2 points and for each lose it gets 1. At least, that's what's happening.

    Ties will be considered as no_wins. As a result, COUNT(*) = (SUM(is_win) - SUM(NOT is_win)). There are no ties in the league

    With those rules, halfbacks won't be shown as abs(Count(*) - (sum(is_win) - sum(ABS( is_win - 1)))) will always be 0.

    What happens if a team has played 3 games and others have played just 1?

    So if a team goes 3-0 and a team is 1-0 the team that is 1-0 should show 1 game back

    Does this make sense....similar to the NBA standings where there are no ties.

  • I could help you get the desired result in SQL Server, however, that might not work for you. You could try on a forum based on MariaDB which is what your DB seems to be on.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply