SQL COUNT Rows from another table

Question

am trying to count number of rows associated with a particular row from another table

SELECT * 
  FROM 
     (
       (SELECT COUNT(*) totalusers 
          FROM mox_admin
             , caspartition 
         WHERE mox_admin.partitionid = caspartition.id
       )
     ) tita
     , caspartition 
 ORDER 
    BY caspartition.id DESC 
 LIMIT 0,5

But the query keep returning the total count of rows in the "moz_admin" table

Here is what i did though i think is not the most efficient method

"SELECT * FROM caspartition ORDER BY caspartition.id DESC LIMIT 0, 5";
//execute the query then loop through
    while($partition_data = mysqli_fetch_array($result)) {
        $partition_id = $partition_data['id'];
    $subquery_sql = "SELECT COUNT(*) AS totalusers FROM moz_admin WHERE partitionid = '$partition_id'";
        $subquery_sql = mysqli_fetch_array(mysqli_query($conn, $subquery_sql));
        $no_of_users = $subquery_sql[totalusers];
    }

The whole point is making this a single SQL query instead querying the table for each row i loop through.

Thanks in advance.

Edit your question and provide sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented May 23, 2017 at 10:45

Gordon Linoff · Accepted Answer · 2017-05-23 10:46:57Z

1

You should combine these into one single query. The query would be:

SELECT cp.id, COUNT(a.partitionid) as cnt
FROM (SELECT *
      FROM caspartition
      ORDER BY caspartition.id DESC
      LIMIT 0, 5
     ) cp LEFT JOIN
     moz_admin a
     ON a.partitionid = cp.id
GROUP BY cp.id;

answered May 23, 2017 at 10:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

user6496388 Over a year ago

Some issues with less reputation so i can't upvote or accept the answer and have it reflect

user6496388 Over a year ago

Just did that now

Collectives™ on Stack Overflow

SQL COUNT Rows from another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related