Two SUM's, one query

[lukep11a]

Hi, I am trying to get two SUM's into one query, one to show the overall total, and the other to show the total of the 5 most recent. Is it possible to put it into one query? I have tried but so far can't seem to get it to work, below I have the code that currently displays only the overall total, in the last column is where I am trying to put the total from the last 5 games. Any help would be very much appreciated. Thanks

 

<table width="500" border="0" cellpadding="0" cellspacing="0">
            <tr class="title">
            <td width="250">Team Name</td>
            <td width="100">Total Points</td>
            <td width="100">last 5 games</td>
            </tr> 
			<?php		
                $query = "SELECT test_teams.team, SUM(test_team_points.points) AS total FROM test_teams, test_team_points WHERE test_teams.selectiongroup = '$group' AND test_teams.teamid = test_team_points.teamid GROUP BY test_teams.teamid ORDER BY test_teams.teamid ASC";
                $result = mysql_query($query) or die(mysql_error());
                
                while($row = mysql_fetch_assoc($result))
                {
                ?>
                    <tr class="teams">
                    <td width="250"><?php echo $row['team']; ?></td>
                    <td width="100"><?php echo $row['total']; ?></td>
                    <td width="100"></td>
                    </tr>
                <?php
                }
                ?>
            </table>

[silkfire]

Remove the team column then perform a SUM on everything.

 

 

[lukep11a]

Thanks for your reply, but I am trying to get the overall total (the total number of points of every entry for each team in test_team_points table) in one column (which I already have, variable set as total) and in the other column I want to place the total of the last 5 entries in the test_team_points table.

[ManiacDan]

You can look into the GROUP BY WITH ROLLUP MySQL command, which will put an extra row at the end of your result set with the "grand total" of the other rows.

 

-Dan

[lukep11a]

thanks for that, will have a look into it!

[Psycho]

I don't think ROLLUP will get you what you want. Your request is kind of misleading, but after reading it a couple of times and looking at your code I think I understand what you want. You want a result set of every team and for every team you want 1) that teams total score and 2) that teams total for the last five games. I think you are going to need to use a sub-query and I don't know how efficient this query would be. I'll try some things out and see if I can come up with a solution then post back.

[ManiacDan]

Ah if that's the case then yeah, you don't want that.

 

A sub-query to find the last 5 games for each team might work. 

[lukep11a]

Hi, sorry sometimes I find it hard to explain what I am trying to do, but yes mjdamato that is exactly what I am after. You put it in much better words than I did, if you could point me in the right direction that would be much appreciated.

[Psycho]

OK, it took some googling, and I'm not sure this is the most efficient method, but it works. I wouldn't have done this, but I like a challenge. This assumes that the points table has a date column (in the query I used the field name "date" but you can change as needed. But, that field should be a FULL date field (i.e. with hours, minutes, and seconds). If there are any records with the exact same date value for a team id you could get incorrect results. But, as long as the records are on different days or are different by at least 1 second you should be fine.

 

SELECT team,
       sum(points1.points) as total_points,
       points2.last_five

FROM test_teams

LEFT JOIN test_team_points AS points1
  ON test_teams.teamid = points1.teamid

LEFT JOIN (SELECT teamid, SUM(points) as last_five
           FROM test_team_points
           JOIN (SELECT t1.teamid, t1.points, t1.date, COUNT(t2.teamid) AS rank
                 FROM test_team_points t1
                 LEFT JOIN test_team_points t2
                   ON t1.teamid = t2.teamid AND t1.date < t2.date
                 GROUP BY t1.teamid, t1.date
           HAVING rank < 5) AS dt
           USING (teamid, date)
           GROUP BY teamid
          ) as points2
  ON test_teams.teamid = points2.teamid

GROUP BY test_teams.teamid

[lukep11a]

Thankyou for your time and help, I have applied the code but it is coming up with an error 'Column 'points' in field list is ambiguous', do you know why this could be?

[Psycho]

Show the exact code you have implemented

[lukep11a]

<form name="form2" action="test-transfer-submit.php" method="get">
    		<table width="550" border="0" cellpadding="0" cellspacing="0">
            <tr class="title">
            <td width="50"> </td>
            <td width="50">id</td>
            <td width="250">Team Name</td>
            <td width="100">Total Points</td>
            <td width="100">last 5 games</td>
            </tr> 
            
			<?php		
                $query = "SELECT team, sum(points1.points) as total_points, points2.last_five
					FROM test_teams
					LEFT JOIN test_team_points AS points1
					ON test_teams.teamid = points1.teamid
					LEFT JOIN (SELECT teamid, SUM(points) as last_five
           				FROM test_team_points
           				JOIN (SELECT t1.teamid, t1.points, t1.date, COUNT(t2.teamid) AS rank
                 		FROM test_team_points t1
                 		LEFT JOIN test_team_points t2
                   		ON t1.teamid = t2.teamid AND t1.date < t2.date
                 		GROUP BY t1.teamid, t1.date
           				HAVING rank < 5) AS dt
           				USING (teamid, date)
           				GROUP BY teamid) as points2
  						ON test_teams.teamid = points2.teamid
					GROUP BY test_teams.teamid";
                $result = mysql_query($query) or die(mysql_error());
                
                while($row = mysql_fetch_assoc($result))
                {
                $team = $row['team'];
                ?>
                    <tr class="teams">
                    <td width="50"><input type="radio" name="<?php echo $row['teamid']; ?>" value="<?php echo $row['teamid']; ?>"></td>
                    <td width="50"><?php echo $row['teamid']; ?></td>
                    <td width="250"><?php echo "<a href='data/teams.php?team=$team' title='$team Team Data' />";
                                        echo $team;
                                        echo "</a>"; ?></td>
                    <td width="100"><?php echo $row['total_points']; ?></td>
                    <td width="100"><?php echo $row['last_five']; ?></td>
                    </tr>
                <?php
                }
                ?>
            </table>
            <input type="submit" value="Submit Transfers">
            </form>

[Psycho]

Odd, it worked for me. Could be due to different versions of MySQL. try providing the appropriate table name before each field name.

 

I don't have the DB I used to test this on this machine, so I can't be sure, but this should be right (although I don't know if it will solve your problem)

SELECT teams.team, sum(points1.points) as total_points, points2.last_five

FROM test_teams AS teams

LEFT JOIN test_team_points AS points1
  ON test_teams.teamid = points1.teamid

LEFT JOIN (SELECT ttp.teamid, SUM(dt.points) as last_five
           FROM test_team_points AS ttp
           JOIN (SELECT t1.teamid, t1.points, t1.date, COUNT(t2.teamid) AS rank
                 FROM test_team_points t1
                 LEFT JOIN test_team_points t2
                   ON t1.teamid = t2.teamid AND t1.date < t2.date
                 GROUP BY t1.teamid, t1.date
                 HAVING rank < 5) AS dt
           USING (ttp.teamid, dt.date)
           GROUP BY ttp.teamid) as points2
  ON test_teams.teamid = points2.teamid

GROUP BY test_teams.teamid

[lukep11a]

Hi, I am now getting this error, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.teamid, dt.date) GROUP BY ttp.teamid) as points2 ON test_teams.tea' at line 16"

 

I am using MySQL version: 5.0.91 if that helps at all.

[Psycho]

OK, I tried the last query and am getting the same error - as I said I didn't have the database I used to test the original query to validate it. But, I went back to the first version and it still works. I am on version 5.0.24a so I don't think the version is a problem.

 

Looking at the error message you got

Column 'points' in field list is ambiguous

 

I'm wondering, do you have a 'points' column in the "test_teams" table? Here are the details of the table structures I used for testing.

CREATE TABLE `test_teams` (
  `teamid` tinyint(4) NOT NULL auto_increment,
  `team` varchar(10) collate latin1_general_ci NOT NULL,
  `selectiongroup` tinyint(2) NOT NULL,
  PRIMARY KEY  (`teamid`)
) 

 

CREATE TABLE `test_team_points` (
  `teamid` smallint(10) NOT NULL,
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `points` varchar(10) collate latin1_general_ci NOT NULL
)

 

I'm sure you have more fields than those, but those are the ones that seemed pertinent to your needs. So, there must be something in your table structure that is different that is causing the failure.

[lukep11a]

Thank you, the fields you created for the test tables were correct and I haven't got any extra fields in them apart from 'selectiongroup' in test_teams, so I went back to your original solution as you suggested. After trying a few things out I finally got it to work simply by changing "LEFT JOIN (SELECT teamid, SUM(points) as last_five" to "LEFT JOIN (SELECT teamid, SUM(test_team_points.points) as last_five".

 

So, it is now giving a result that I thought was correct but after looking at it more closely, I have noticed that it seems to be doing a SUM of the points from the last 3 games rather than 5. The overall total is correct though.

[lukep11a]

I've just changed "HAVING rank < 5" to "HAVING rank < 7" and it seems to have done the trick, not sure how though!