CODEWITHSUNDEEP
mysqlselectcount
Momen Zalabany
Momen Zalabany

Reputation: 9007

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 ?

Upvotes: 6

Views: 7966

Answers (4)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

Try this:

select
   p.id AS `id`,
   p.name AS `name`, 
   IFNULL(v.TheCount, 0) TheCount
from personal p
LEFT JOIN
( 
  SELECT user_id, COUNT(*) TheCount
  FROM visits v
  GROUP BY user_id
) v ON v.user_id = p.Id;

Upvotes: 1

Sahal
Sahal

Reputation: 4136

That will work with LEFT JOIN tables personal with visit.

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

If you want only user which are having atleast visit count 1, do RIGHT JOIN OR HAVING in Group By

SELECT 
    Pe.id AS id, Pe.name AS name, COUNT(v.user_id) number_visit
FROM
    personal Pe
RIGHT JOIN
    visit Vi ON Vi.user_id = Pe.id
GROUP BY Pe.id

OR

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 HAVING number_visit > 1

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

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

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425321

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

Upvotes: 20

Related Questions