num rows in single query-CodePudding

I have two tables and I want to count the number of rows in a single query.

Here is what I have so far and is working but has multiple query's and is not ok.

PHP Code:

     $q1=$db->query("SELECT id FROM edev_useri");
     $q2=$db->query("SELECT groupid FROM edev_useri WHERE groupid = 6");
     $q3=$db->query("SELECT groupid FROM edev_useri WHERE groupid = 7");
     $q4=$db->query("SELECT id FROM edev_cereri");
       $useri  = $db->numRows($q1);
       $dev    = $db->numRows($q2);
       $ben    = $db->numRows($q3);
       $cereri = $db->numRows($q4);
     $data=array();
     $dashStats = array(
      'useri'  => $useri,
      'dev'    => $dev,
      'ben'    => $ben,
      'cereri' => $cereri
      ); 
     $data[]=$dashStats;
     $json_data = array('dashStats' => $data); 
      echo json_encode($json_data);

CodePudding user response：

This is not pretty, and perhaps you could do this a better way, but it does the job.

untested ofc

SELECT
    (SELECT COUNT(*) FROM edev_useri) AS `useri`,
    (SELECT COUNT(*) FROM edev_useri WHERE groupid = 6) AS `dev`,
    (SELECT COUNT(*) FROM edev_useri WHERE groupid = 7) AS `ben`,
    (SELECT COUNT(*) FROM edev_cereri) AS `cereri`
;

CodePudding user response：

Note: It's a bit unclear what the actual problem is, but from what I understood, it's about reducing this to one SQL query only. This means that it requires a different query, so this answer is talking about ways to do it in SQL. How to interpret the different results in PHP is not covered.

You can use COUNT(*) (returning the count of rows as single value) and UNION SELECT (allowing you to combine multiple SELECTs in one, as long as the number of columns is the same in all of them):

SELECT COUNT(*) AS `count` FROM edev_useri
UNION SELECT COUNT(*) FROM edev_useri WHERE groupid = 6
UNION SELECT COUNT(*) FROM edev_useri WHERE groupid = 7
UNION SELECT COUNT(*) FROM edev_cereri

Then you get a result like this:

 ----- 
|count|
 ----- 
|    1|
|    2|
|    3|
|    4|
 -----

Or you could use SUM to count all the three things related to edev_useri in one row:

SELECT
  COUNT(*) AS `count`,
  COALESCE(SUM(groupid = 6), 0) AS `group6Count`
  COALESCE(SUM(groupid = 7), 0) AS `group7Count`
FROM edev_useri
UNION SELECT COUNT(*), NULL, NULL FROM edev_cereri

(This works because groupid = 6 will evaluate to 1 when the condition is true, so the SUM of all those 1s will equal to the count of rows matching the condition. The extra COALESCE is used just so that in case all rows have groupid as NULL - or there are no rows - the result would still be 0 and not NULL.)

Then you get a result like this:

 ----- ----------- ----------- 
|count|group6Count|group7Count|
 ----- ----------- ----------- 
|    1|          2|          3|
|    4|       NULL|       NULL|
 ----- ----------- -----------

Or, if you prefer it all in one row, you can do the 4th item as subquery:

SELECT
  COUNT(*) AS `userCount`,
  COALESCE(SUM(groupid = 6), 0) AS `group6Count`,
  COALESCE(SUM(groupid = 7), 0) AS `group7Count`,
  (SELECT COUNT(*) FROM edev_cereri) AS `requestCount`
FROM edev_useri

Then you get a result like this:

 ----- ----------- ----------- ------------ 
|count|group6Count|group7Count|requestCount|
 ----- ----------- ----------- ------------ 
|    1|          2|          3|           4|
 ----- ----------- ----------- ------------