Union and Group By

fr600

When I use this:

$res = "(SELECT users.username, old_news.postedby, SUM(old_news.views) AS views FROM users LEFT JOIN old_news ON users.id = old_news.postedby GROUP BY users.username)
  UNION (SELECT users.username, new_news.postedby, SUM(new_news.views) AS views FROM users LEFT JOIN new_news ON users.id = new_news.postedby GROUP BY users.username)
  ORDER BY views DESC";

$query = mysql_query($res);
while($row = mysql_fetch_array($query) ) {

$username = $row["username"];
$views = $row["views"];

echo "<tr><td>$username</td><td align=right>$views</td></tr>";

I get this result:

John        130
Mark        110
Samy        80
Andy        65
Mark        70
Andy        15
Samy        10

But when I try to group the result like this:

Mark        180
John        130
Samy        90
Andy        80

By using this code:

$res = "(SELECT users.username, old_news.postedby, SUM(old_news.views) AS views FROM users LEFT JOIN old_news ON users.id = old_news.postedby GROUP BY users.username)
  UNION (SELECT users.username, new_news.postedby, SUM(new_news.views) AS views FROM users LEFT JOIN new_news ON users.id = new_news.postedby GROUP BY users.username)
  GROUP BY users.username ORDER BY views DESC";

$query = mysql_query($res);
while($row = mysql_fetch_array($query) ) {

$username = $row["username"];
$views = $row["views"];

echo "<tr><td>$username</td><td align=right>$views</td></tr>";

I get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in d:\Server\Apache\htdocs\newsphp\stat.php on line 50

Any idea how to fix this?

MikeSnead

Start by changing the line with mysql_query to this:

$query = mysql_query($res) or die(mysql_error());

There seems to be a problem with the query and the above addition should help you find the problem.

Lars_Berg

select username,postedby,sum(views) as views from
(SELECT users.username, 
       old_news.postedby, 
       old_news.views 
  FROM users LEFT JOIN old_news ON users.id = old_news.postedby 
UNION all
  SELECT users.username, 
         new_news.postedby, 
         new_news.views
   FROM users LEFT JOIN new_news ON users.id = new_news.postedby ) dt
GROUP BY username,postedby
ORDER BY views DESC

fr600

1st reply helped me to find out the problem and 2nd reply totally solved my problem.

Thanks a lot guys.