COUNT() returned from multiple tables?

PHP
1

hello

is there a way to get SUM of COUNT(*) AS user_id of two tables in one query?
this code shows only table rtv ID : NAME but I need ID: NAME + COUNT SUMMARY

$sql = "SELECT blah.* FROM rtv AS blah LEFT JOIN (SELECT mes_id_fk, COUNT(*) AS user_id FROM vote_ip WHERE date> (NOW() - INTERVAL 2592000 SECOND) GROUP BY mes_id_fk) AS top_voted ON blah.id = top_voted.mes_id_fk ORDER BY top_voted.user_id DESC limit 5";
		$result = mysql_query($sql) or die(mysql_error());
		while($row = mysql_fetch_array($result)){
		
		$list .= "".$row[id].": ".$row[naslov]." <br>";
	} 
		echo $list;
2 

select sum(columnName)
from
(
select sum(columnName1) as columnName
from
table1
union all
select sum(columnName2) as columnName
from
table2
)
3

that’s not what I need my code works great, just I do not know to get COUNT(*) AS user_id summed for each id

4

SELECT blah.*, top_voted.user_id

5

You’re right Guido but That’s the Problem
I need top_voted.user_id in line below but nothing works (

$list .= "".$row[id].": ".$row[naslov]." <br>";
6

Try quoting the array keys, and concatenating the strings correctly:


$list .= $row['id'] . ": " . $row['naslov'] . "<br>";

You can always try and use:


var_dump($row) ;

to see what is being returned in $row.

EDIT ps I am not addressing your entire problem, just responding to some syntax errors in your last posting.

7

Kevin’s answer is almost the correct one (DEPENDING on the actual desired result… i think you want the individual sums as well? Could be reading it wrong.), but perhaps explaining your tables a little bit more might help; why do you want the count of two seperate tables? There’s probably a better way to design the DB/query to get what you want.

SELECT SUM(sub1.field1, sub2.field2),sub1.field1,sub2.field2 FROM 
(SELECT COUNT(*) AS field1 FROM table1) AS sub1,
(SELECT COUNT(*) AS field2 FROM table2) AS sub2;
8

OK StarLion

I have one sql table that looks like this called “users”:

TABLE USERS

-------------------------------
 user_id    |  username
-------------------------------
 1          |   tom
 2          |   john
 3          |   tim

and another called “vote” that stores either upvotes or downvotes (downvotes deactivated).

TABLE VOTE

------------------------------
id     |   user_id   |   up  |
------------------------------
1      |  1	     |   Y   |
2      |  1	     |   Y   |
3      |  2	     |   Y   |
4      |  2	     |   Y   |
5      |  3	     |   Y   |

how would I go about ordering vote by tim tom und johh by the number of (upvotes) ?

I need something like this code below

  1. tom has 200 voted
  2. tim has 180 voted
  3. john has 178 voted
9

Oh is that all? Much more simple then.

SELECT users.username,COUNT(vote.up) AS votes_received 
FROM users 
LEFT JOIN vote 
ON users.user_id = vote.user_id AND vote.up = 'Y' 
GROUP BY users.user_id 
ORDER BY votes_received DESC;

see, giving us all the information gets you better answers! :wink:

10

^ this

:award:

11

:smiley:

This Code Work!!

Big Thanks to StarLion :slight_smile: