mysql query multiple tables and count row

Question

I´m new to MySql and I´m having a problem ordering the result of a query.

I have 2 tables:

Table1 contains the users of my system

userId  userName
01      Ken
02      John
03      Bob
04      Steve

Table2 contains the followers of each user

userId, FollowerId.
02      01
01      02
02      03
02      04

so, in this case John has 3 followers: Ken, Bob and Steve.

I would like to generate a third table with 2 columns: users of the database and Number of followers per User. I would like to have the table ordered by Number of followers.

  userId NrFollowers
  02    3
  01    1
  03    0
  04    0

at the moment I´m using the following code

    $stmt = $conn->prepare("SELECT userId, userName FROM Table1");
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($userId, $userName);
        while($stmt->fetch()) {
                //count number followers
                $stmtfl = $conn->prepare("SELECT * FROM Table2 WHERE userId = (?)");
                $stmtfl->bind_param("s", $userId);
                $stmtfl->execute();
                $stmtfl->store_result();
                $stmtfl->fetch();
                $followCount= $stmtfl->num_rows;

                //now I have $userName and $NrFollowers

This solutions is not optimised as required an loop for each databases`s user and does not allow to order the users by number of followers.

Can someone please help me to write the proper Mysql query?

Blank · Accepted Answer · 2017-02-25 08:04:48Z

1

For sql query, you can try this:

select
    tbl1.userId,
    count(distinct tbl2.FollowerId) as NrFollowers
from tbl1
left join tbl2
on tbl1.userId = tbl2.userId
group by tbl1.userId
order by count(distinct tbl2.FollowerId) desc

See demo here.

edited Feb 25, 2017 at 8:04

answered Feb 25, 2017 at 7:58

Blank

12.4k1 gold badge16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zorzihit Over a year ago

Thank you very much. I had a look on the demo. It is exactly what I was looking for :)

Naincy · Accepted Answer · 2017-02-25 07:59:29Z

1

SELECT u.usersId, COUNT(f.FollowerId) AS NrFollowers
FROM users u
LEFT JOIN followers f ON f.userId = u.userId
GROUP BY u.userId

answered Feb 25, 2017 at 7:59

Naincy

2,9431 gold badge15 silver badges21 bronze badges

1 Comment

zorzihit Over a year ago

Thank you very much

sandeep rawat · Accepted Answer · 2017-02-25 08:03:31Z

1

use below code..

SELECT u.usersId, coalesce(COUNT(f.FollowerId) ,0)AS NrFollowers
FROM users u
LEFT JOIN followers f ON f.userId = u.userId
GROUP BY u.userId
order by coalesce(COUNT(f.FollowerId) ,0) desc

answered Feb 25, 2017 at 8:03

sandeep rawat

4,9571 gold badge22 silver badges40 bronze badges

1 Comment

zorzihit Over a year ago

Thank you very much

Collectives™ on Stack Overflow

mysql query multiple tables and count row

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related