COUNT() returned from multiple tables?

[doca]

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; 

[K. Wolfe]

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

[doca]

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

[guido2004]

SELECT blah.*, top_voted.user_id

[doca]

SELECT blah.*, top_voted.user_id

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>"; 

[Cups]

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>"; 

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.

[StarLion]

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;

[doca]

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;

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

[StarLion]

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!

[r937]

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

^ this

[doca]

trophy-icon.png

This Code Work!!

Big Thanks to StarLion