Results of two queries into one html table

[xtrad]

Hi,

 

I have a table called LeagueResults1 with the following five columns:

 

ResultID, Machine, Player, Score, Meet

 

I have an SQL query that finds the highest score per machine:

 

$query = "SELECT f.machine, f.player, f.score, f.meet FROM (SELECT machine, MAX(score) AS maxscore FROM LeagueResults1 GROUP BY machine) AS X INNER JOIN LeagueResults1 AS f ON f.machine = x.machine AND f.score = x.maxscore GROUP BY machine";

 

I display the results in an html table with the following headings:

 

Machine, Highscore, Player, Meet

 

I also have an SQL query that finds the average score per machine:

 

$query = "SELECT machine,AVG(score) AS avgscore FROM LeagueResults1 WHERE score > 0 GROUP BY machine";

 

I display the results in an html table with the following headings:

 

Machine, Average Score

 

However i now want to use the results of both queries in the same html table with the following headings:

 

Machine, Highscore, Player, Meet, Average Score

 

I can't figure out how to get the results from the two separate queries into the same html table.

 

Actually, i'm not sure whether the solution is to rewrite my two SQL queries as a single query, or whether the two queries should be kept and PHP used to organise the results.  I've tried both but have still not been successful.

 

Any help would be much appreciated.

[fenway]

You can't use an aggregate expression in the WHERE clause -- move it to HAVING.

[xtrad]

Thanks for your reply.

 

Do you mean changing this query:

 

$query = "SELECT machine,AVG(score) AS avgscore FROM LeagueResults1 WHERE score > 0 GROUP BY machine";

 

... to this query:

 

$query = "SELECT machine,AVG(score) AS avgscore FROM LeagueResults1 GROUP BY machine HAVING MIN(score) > 0";

 

I did this but now it doesn't list the average score for every machine.  There are currently 23 machines in the table, but the new query only lists 17 of them.  I've looked at the machines that aren't listed but can't see why they are missing.

 

The original query listed all 23 machines.

 

 

[fenway]

Well, you can still use the alias "avgscore" in the HAVING clause -- but actually, I didn't realize that score was a real column.

 

Re-reading your original post, why not simply use a column subquery to grab the AVG()?

[xtrad]

Thanks for your suggestions.

 

However, i still haven't been able to get it working how i'd like.

 

A quick re-cap:  I want the results of the following two queries to be used in the same html table.  The first query finds the high score for every machine.  The second query finds the average score on every machine.  I know that both of these queries work on their own as i am using them already.  It's when i try to combine the results into one html table that i have problems:

 

$query = "SELECT f.machine, f.player, f.score, f.meet FROM (SELECT machine, MAX(score) AS maxscore FROM LeagueResults1 GROUP BY machine) AS X INNER JOIN LeagueResults1 AS f ON f.machine = x.machine AND f.score = x.maxscore GROUP BY machine";

$result = mysqli_query($cxn,$query) or die ("Couldn't execute query");

$nrows = mysqli_num_rows($result);

for ($i=0;$i<$nrows;$i++)

{
$row = mysqli_fetch_assoc($result);
extract($row);

$score = number_format($score);
}

$query = "SELECT machine,AVG(score) AS avgscore FROM LeagueResults1 WHERE score > 0 GROUP BY machine";  

$result = mysqli_query($cxn,$query) or die ("Couldn't execute query");

$nrows = mysqli_num_rows($result);

for ($i=0;$i<$nrows;$i++)

{
$row = mysqli_fetch_assoc($result);
extract($row);

$avgscore = number_format($avgscore);

echo "<tr class='border'>\n
	<td class='firstcolumn'>$machine</td>\n
	<td style='text-align: right' class='scores'>$score</td>\n
	<td>$player</td>\n
	<td>$meet</td>\n
	<td style='text-align: right' class='scores'>$avgscore</td>\n
	</tr>\n";
echo "<tr><td colspan='5' class='lines'><hr class='dashes'></td></tr>\n";
}
echo "<table>\n";

 

I've also attached a screenshot of the html table that the above code produces and will explain this below: 

 

- The machine column correctly lists all machines in alphabetical order. 

- The High Score, Player and Meet columns are correct only for the last row of the table (the Vector machine), but they have been repeated on every row of the table.

- The UKPBL Average column correctly displays the average score for each of the machines in the Machine column.

 

So, the problem is that although i am getting the correct High Score, Player and Meet for the Vector machine/row, it's just repeating it for all the other machines/rows.

 

If you are patient enough to offer some advice i would really appreciate it!

 

[attachment deleted by admin]

[fenway]

Well, I still don't see why a simple scalar subquery won't work.

[xtrad]

Thanks again for taking the time to reply.

 

So, i had the following two queries:

 

$query = "SELECT f.machine, f.player, f.score, f.meet
FROM (SELECT machine, MAX(score) AS maxscore FROM LeagueResults1 GROUP BY machine) 
AS X INNER JOIN LeagueResults1 AS f ON f.machine = x.machine AND f.score = x.maxscore GROUP BY machine";

$query = "SELECT machine,AVG(score) AS avgscore FROM LeagueResults1 WHERE score > 0 GROUP BY machine";

 

And i've been trying to combine them into one query.  I've tried making the second query (the query that finds the average scores for each machine) a subquery of the first query:

 

$query = "SELECT f.machine, f.player, f.score, f.meet, (SELECT AVG(score) AS avgscore FROM LeagueResults1 WHERE score > 0) 
FROM (SELECT machine, MAX(score) AS maxscore FROM LeagueResults1 GROUP BY machine) 
AS X INNER JOIN LeagueResults1 AS f ON f.machine = x.machine AND f.score = x.maxscore GROUP BY machine";

 

But it just returns a zero for every row in the average column.

 

I have tried other ways of writing the query but then it completely fails to execute.  I know what i am doing is obviously not right, but despite researching and thinking about it, i can't figure out how to combine the two queries properly.

[fenway]

Too complicated -- prove that getting the AVG as a subquery works first.

[xtrad]

I solved this problem using PHP in the end.  It was easier to do it that way.

 

Thanks for your replies/suggestions though - they made me think a lot more about what i was trying to do.