[SOLVED] SQL Database question

[debecc]

MySQl version 4.1.21 standard

PHP 4.4.2

 

SQL Table- gameresults:

 

gameresults_id

'win_team

lose_team

win_score

lose_score

hi_lites

date

 

Hi,

 

I have a question. I am new to sql and php. i have created a table to keep track of gameresults from a softball league. i would like to have an area on the page to show each teams win-lose record.

At the moment i have successfully gotten a query to show a count of each teams wins. i would like to get the information to display as teamname (3 wins - 4 loses) and show all 9 teams in order of first palce, second place etc.

my query to get wins is:

SELECT `win_team` AS team, COUNT(`win_team`) AS wins FROM `gameresults` GROUP BY `win_team` ORDER BY 'wins' DESC

 

Is there a way i can get and show loses also.

i know this is not the best database structure. i had to put it together fast and am working on making the database work with a games schedule etc.

 

the page address is http:www.coventrywomenssoftball.com

I use this table for league standing section and game results section.

 

 

Thanks for any help

 

Deb

 

Deb

[Wildbug]

Since you have MySQL 4.1+, you should be able to use a subquery:

 

SELECT Team,SUM(Team=win_team) AS Wins,SUM(Team=lose_team) AS Losses FROM (SELECT win_team AS Team FROM gameresults UNION SELECT lose_team FROM gameresults) AS t1 JOIN gameresults AS t2 GROUP BY Team ORDER BY Wins DESC,Losses

[debecc]

 

That's great!!!!

 

It did the trick

 

THANKS SOO MUCH!!

 

As i said i am very new to SQL and can't wait to actually understand everything you just wrote!!

 

 

 

Thanks

again

Deb

[Wildbug]

Test set:

mysql> SELECT * FROM gameresults;
+----------------+---------------------+---------------------+-----------+------------+------------+
| gameresults_id | win_team            | lose_team           | win_score | lose_score | date       |
+----------------+---------------------+---------------------+-----------+------------+------------+
|              1 | Boyers Badgers      | Louisville Leeches  |        10 |          5 | 2007-05-05 |
|              2 | Boyers Badgers      | Tasker Bluejays     |         6 |          5 | 2007-06-01 |
|              3 | Tasker Bluejays     | Stonersville Stumps |         4 |          0 | 2007-05-31 |
|              4 | Louisville Leeches  | Stonersville Stumps |         3 |          1 | 2007-06-02 |
|              5 | Maskington Bulldogs | Boyers Badgers      |        11 |         10 | 2007-06-04 |
|              6 | Clodston Clovers    | Stonersville Stumps |         5 |          0 | 2007-06-06 |
|             10 | Maskington Bulldogs | Stonersville Stumps |         9 |          0 | 2007-06-10 |
|              7 | Boyers Badgers      | Sillyville Sliders  |         7 |          4 | 2007-06-05 |
|              8 | Louisville Leeches  | Boyers Badgers      |         3 |          2 | 2007-06-06 |
|              9 | Maskington Bulldogs | Tasker Bluejays     |         5 |          4 | 2007-06-08 |
+----------------+---------------------+---------------------+-----------+------------+------------+
10 rows in set (0.01 sec)

 

Explanation (color coded):

 

SELECT Team,SUM(Team=win_team) AS Wins,SUM(Team=lose_team) AS Losses FROM (SELECT win_team AS Team FROM gameresults UNION SELECT lose_team FROM gameresults) AS t1 JOIN gameresults AS t2 GROUP BY Team ORDER BY Wins DESC,Losses

 

First, you need to find ALL the teams in the table, not just winners, not just losers.  We'll do this by selecting the winners column, selecting the losers column, and treating those as a single column of results via a UNION.  We've also aliased win_team as "Team" -- the following UNIONed SELECTs will also take that alias by default, meaning that lose_team will also be returned as "Team."

mysql> SELECT win_team AS Team FROM gameresults UNION SELECT lose_team FROM gameresults;
+---------------------+
| Team                |
+---------------------+
| Boyers Badgers      |
| Tasker Bluejays     |
| Louisville Leeches  |
| Maskington Bulldogs |
| Clodston Clovers    |
| Stonersville Stumps |
| Sillyville Sliders  |
+---------------------+
7 rows in set (0.00 sec)

 

We'll use this as a subquery, and it needs an "alias" -- that is, another name so that it can be distinguished from other tables or sets of results.  It's alias is "t1".

 

The results of this subquery will be JOINed to the original table, gameresults.  (I'll add an extra JOIN clause so the result set isn't too large.)

 

mysql> SELECT *,Team=win_team AS Wins,Team=lose_team AS Losses FROM (SELECT win_team AS Team FROM gameresults
UNION SELECT lose_team FROM gameresults) AS t1 JOIN gameresults AS t2 ON (t1.Team=t2.win_team OR t1.Team=t2.lose_team);
+---------------------+----------------+---------------------+---------------------+-----------+------------+-----------+------+--------+
| Team                | gameresults_id | win_team            | lose_team           | win_score | lose_score |date       | Wins | Losses |
+---------------------+----------------+---------------------+---------------------+-----------+------------+-----------+------+--------+
| Boyers Badgers      |              1 | Boyers Badgers      | Louisville Leeches  |        10 |          5 |2007-05-05 |    1 |      0 |
| Louisville Leeches  |              1 | Boyers Badgers      | Louisville Leeches  |        10 |          5 |2007-05-05 |    0 |      1 |
| Boyers Badgers      |              2 | Boyers Badgers      | Tasker Bluejays     |         6 |          5 |2007-06-01 |    1 |      0 |
| Tasker Bluejays     |              2 | Boyers Badgers      | Tasker Bluejays     |         6 |          5 |2007-06-01 |    0 |      1 |
| Tasker Bluejays     |              3 | Tasker Bluejays     | Stonersville Stumps |         4 |          0 |2007-05-31 |    1 |      0 |
| Stonersville Stumps |              3 | Tasker Bluejays     | Stonersville Stumps |         4 |          0 |2007-05-31 |    0 |      1 |
| Louisville Leeches  |              4 | Louisville Leeches  | Stonersville Stumps |         3 |          1 |2007-06-02 |    1 |      0 |
| Stonersville Stumps |              4 | Louisville Leeches  | Stonersville Stumps |         3 |          1 |2007-06-02 |    0 |      1 |
| Boyers Badgers      |              5 | Maskington Bulldogs | Boyers Badgers      |        11 |         10 |2007-06-04 |    0 |      1 |
| Maskington Bulldogs |              5 | Maskington Bulldogs | Boyers Badgers      |        11 |         10 |2007-06-04 |    1 |      0 |
| Clodston Clovers    |              6 | Clodston Clovers    | Stonersville Stumps |         5 |          0 |2007-06-06 |    1 |      0 |
| Stonersville Stumps |              6 | Clodston Clovers    | Stonersville Stumps |         5 |          0 |2007-06-06 |    0 |      1 |
| Maskington Bulldogs |             10 | Maskington Bulldogs | Stonersville Stumps |         9 |          0 |2007-06-10 |    1 |      0 |
| Stonersville Stumps |             10 | Maskington Bulldogs | Stonersville Stumps |         9 |          0 |2007-06-10 |    0 |      1 |
| Boyers Badgers      |              7 | Boyers Badgers      | Sillyville Sliders  |         7 |          4 |2007-06-05 |    1 |      0 |
| Sillyville Sliders  |              7 | Boyers Badgers      | Sillyville Sliders  |         7 |          4 |2007-06-05 |    0 |      1 |
| Boyers Badgers      |              8 | Louisville Leeches  | Boyers Badgers      |         3 |          2 |2007-06-06 |    0 |      1 |
| Louisville Leeches  |              8 | Louisville Leeches  | Boyers Badgers      |         3 |          2 |2007-06-06 |    1 |      0 |
| Tasker Bluejays     |              9 | Maskington Bulldogs | Tasker Bluejays     |         5 |          4 |2007-06-08 |    0 |      1 |
| Maskington Bulldogs |              9 | Maskington Bulldogs | Tasker Bluejays     |         5 |          4 |2007-06-08 |    1 |      0 |
+---------------------+----------------+---------------------+---------------------+-----------+------------+------------+------+--------+
20 rows in set (0.00 sec)

 

The condition "Team=win_team" returns 1 if the "Team" column matches the "win_team" column and 0 if not.  The SUM() function adds these together; that's how you get a count of how many times "Team" was the winner.  The Losses column is similar.

 

Team is taken from the subquery (t1.Team)

 

Finally, it all comes together with the GROUP BY and ORDER BY.  The GROUP BY allows the use of the SUM() functions, which are "aggregate" functions -- they act on a group of values.  So, the SUM() of the wins and losses are added up and grouped by "Team."  The results are ordered by (1) Wins, larger number at top, and (2) Losses, smaller number at top.  (I think that's how you order teams, right?)

 

Final result:

mysql> SELECT Team,SUM(Team=win_team) AS Wins,SUM(Team=lose_team) AS Losses FROM (SELECT win_team AS Team FROM
gameresults UNION SELECT lose_team FROM gameresults) AS t1 JOIN gameresults AS t2 GROUP BY Team ORDER BY Wins
DESC,Losses;
+---------------------+------+--------+
| Team                | Wins | Losses |
+---------------------+------+--------+
| Maskington Bulldogs |    3 |      0 |
| Boyers Badgers      |    3 |      2 |
| Louisville Leeches  |    2 |      1 |
| Clodston Clovers    |    1 |      0 |
| Tasker Bluejays     |    1 |      2 |
| Sillyville Sliders  |    0 |      1 |
| Stonersville Stumps |    0 |      4 |
+---------------------+------+--------+
7 rows in set (0.00 sec)