Jump to content
alphamoment

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

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!!

Share this post


Link to post
Share on other sites

try

SELECT
    t2.PlayerName
  , COUNT(t1.ID)
FROM Table2 t2
    LEFT JOIN Table1 ON t2.PlayerID = t1.ID 

Share this post


Link to post
Share on other sites

Hello Barand, Thanks for the quick reply. 

I've tried your Query and the results displayed only 1 row

ID        COUNT
4017       85

Not quite sure how it got 85 when there is only 3 listings for ID 4017. Hmmmm

 

Share this post


Link to post
Share on other sites

Oops. I forgot the GROUP BY

SELECT
    t2.PlayerName
  , COUNT(t1.ID)
FROM Table2 t2
    LEFT JOIN Table1 ON t2.PlayerID = t1.ID 
GROUP BY PlayerName
  • Like 1

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

That's a lot cleaner thanks! If I wanted to limit the results to 50 rows being displayed how can I come about that? (Sorry for all the questions)

Share this post


Link to post
Share on other sites

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.