Get multiple "number of rows" in mysql

Question

i have a table of questions, each question have a level for it's hardness from 1 to 4, i want to get number of rows of question of same level.

this is what i tried:

   $result1 = mysqli_query($con,"SELECT * from questions WHERE level=1");
   $result2 = mysqli_query($con,"SELECT * from questions WHERE level=2");
   $result3 = mysqli_query($con,"SELECT * from questions WHERE level=3");
   $result4 = mysqli_query($con,"SELECT * from questions WHERE level=4");

   $n1 = mysqli_num_rows($result1);
   $n1 = mysqli_num_rows($result2);
   $n1 = mysqli_num_rows($result3);
   $n1 = mysqli_num_rows($result4);

well, it's so bad and use four queries to do that.
another way in my mind was to use php :

   $level = array(0,0,0,0);

   $result = mysqli_query($con,"SELECT * from questions");
   while($res = mysqli_fetch_array($result)){
      switch ($res['level']){
     case '1':
        $level[0]++;
        break;

     case '2':
        $level[1]++;
        break;

     case '3':
        $level[2]++;
        break;

     case '4':
        $level[3]++;
        break;

     default:
        echo "WTF";

      }
   }
   echo "LEVEL 1 :$level[0]<br>LEVEL 2 :$level[1]<br>LEVEL 3 :$level[2]<br>LEVEL 4 :$level[3]<br>";

this use php and not mysql, but looks faster. is there any other FASTER way to achieve this? what's the best way?

Gordon Linoff · Accepted Answer · 2014-11-11T21:19:47.710

6

I think you just want a group by query:

SELECT level, count(*)
FROM questions
GROUP BY level;

EDIT:

It might be easier if you do this using conditional aggregation to get one row of data:

SELECT SUM(level = 1) as num1, SUM(level = 2) as num2,
       SUM(level = 3) as num3, SUM(level = 4) as num4 
FROM questions;

This produces four variables in one row, which you can readily access in php.

edited Nov 11 '14 at 21:19

answered Nov 11 '14 at 21:09

Gordon Linoff

1,242,037
58
646
786

I started writing this longer query with `SUM` and `CASE` in the `SELECT` clause á la [this question](http://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query), then your answer popped up. Sometimes I forget to keep it simple, dammit. – sjagr Nov 11 '14 at 21:11
@gordon-linoff thanks for answer but how can i access level1,2,3 and 4 counts? i used var dump to see result and there was only level1 – Jafar Akhondali Nov 11 '14 at 21:25
thanks it worked perfectly,but can you explain SUM(level = 1) ? i know the job of sum function but what's the meaning of level = 1? – Jafar Akhondali Nov 11 '14 at 21:39
`level = 1` is a boolean expression. MySQL (but not other databases) treats this as a value of `0` for false and `1` for true. So, you can count the number of matches just by adding the expression, a handy feature in MySQL. – Gordon Linoff Nov 12 '14 at 00:28

Get multiple "number of rows" in mysql

1 Answers1