Jump to content

Can't get my innerjoin to work with select sum groupby


alphamoment
Go to solution Solved by Barand,

Recommended Posts

Hello. I'm trying to get this piece of code finished but it's not going my way, if anyone could help me out that would be great.
Here whats I'm trying to do: 

Table1                                   Table2

----------------            ---------------------------------      
      ID       |            | PlayerID        |  PlayerName |
----------------             --------------------------------
1393           |            | 1393              | Player1   |
2097           |            | 2097              | Player2   |
3888           |            | 3888              | Player3   |
3888           |            | 4017              | Player4   |
3888           |            ---------------------------------
4017           |                
4017           |                
4017           |                
----------------

I Want to Count the entries in Table1 (3888 has 3 entries so it will display like "3888: 3")
Then I want to join Table1 and Table2 using the ID so I can get the players name (3888=Player3 so it would display like Player3 : 3)

Here's the code I'm using:

<?php
//connect to db

$query = "SELECT * FROM Table1 INNER JOIN Table2 WHERE Table1.PlayerID = Table2.ID";

$query2 = "SELECT ID, SUM(ID=0) AS n0, SUM(ID=1) AS n1, COUNT(*) AS total FROM Table1 GROUP BY ID";

$result = mysql_query($query) or die($query."<br/><br/>".mysql_error());
$result2 = mysql_query($query2) or die($query2."<br/><br/>".mysql_error());

echo "<table border='1'>
<tr>
<th>PlayerName</th>
<th>Entries</th>
</tr>";
while($row2 = mysql_fetch_array($result2))
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['ID'] . "</td>";
echo "<td>" . $row['PlayerName'] . "</td>";
echo "<td>" . $row2['Total'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($link);
?>

If I use $query on its own it joins perfectly, If I use $query2 on its own, it displays the results exactly how I want them listed but with the ID instead of the PlayerName, 
I tried putting them both together as shown above but I can't get them to work together.

What I want my end Result to be

Player1            1

Player2            1

Player3            3

Player4            3

How it keeps coming out;

1393             1  

2097             1
 
3888             3

4017             3

 Can anyone see where I'm going wrong? Thank you!!

Link to comment
Share on other sites

Haha, thanks! Works perfect, Thank you so much Barand. One last question; for the echo I've used

$row['COUNT(t1.ID)']

How can I get it to ORDER BY  the highest count descending 

ORDER BY t1.COUNT(t1.ID) DESC

This doesn't seem to do the trick!

Edit: I got it working using;

ORDER BY COUNT(*) DESC

Thank you!

Edited by alphamoment
Link to comment
Share on other sites

If you give the count an alias, say 'total', then you can reference that eg

echo $row['total'];

and also order by the total too

SELECT
    t2.PlayerName
  , COUNT(t1.ID) as total
FROM Table2 t2
    LEFT JOIN Table1 ON t2.PlayerID = t1.ID 
GROUP BY PlayerName
ORDER BY total DESC
  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.