Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql show Count of rows from other table in each row

select `personal`.`id` AS `id`,
`personal`.`name` AS `name`,
(select count(visit.id) 
      from visit,personal 
        where visit.user_id=personal.id) as count 
from personal;

im trying to get all users and the counts of visits they did.

the result i get is all users but the count column contain same value (not specific to that row id).

what am i doing wrong here ? how to tell mysql to user this row id ?

is compound select optimum way to do it or is there a better way ?

like image 539
Zalaboza Avatar asked Jan 15 '13 12:01

Zalaboza


2 Answers

SELECT  p.id, p.name, COUNT(v.user_id)
FROM    personal p
LEFT JOIN
        visit v
ON      v.user_id = p.id
GROUP BY
        p.id

You may also use subselect of course (for instance if you have ANSI GROUP BY compatibility on):

SELECT  p.id, p.name,
        (
        SELECT  COUNT(*)
        FROM    visit v
        WHERE   v.user_id = p.id
        )
FROM    personal p
like image 65
Quassnoi Avatar answered Oct 02 '22 20:10

Quassnoi


try this

 SELECT
Pe.id AS id,Pe.name AS name,COUNT(v.user_id) number_visit
FROM personal Pe
LEFT JOIN visit Vi
   ON Vi.user_id= Pe.id
GROUP BY Pe.id
like image 37
echo_Me Avatar answered Oct 02 '22 19:10

echo_Me