Getting the count of rows in each category in a MySql Table

Let me say, I have two tables category and questions.
In questions table I have few or more questions under the same category and the table has questions of various categories.

I need the count of rows in each category under the question table.

Eg., category_count = [10,2,4,7,23,15]

What I did to get the count is,

if($model){
        $i = 0; $j = 0;
        $category_count = 0;
        $count [] = null;

        foreach($model as $question) {
            $category_count++;
            if(isset($output))
                if($question->catid != $output[$i-1]['cat'] ){
                    $count[$j] = $category_count;
                    $j++;
                    $category_count = 0;
                }
            $output[$i++] = ['id' => $question->id, 'cat' => $question->catid, 'title' => $question->title];
        }
        $count[$j]=$category_count;
        $final = ['count'=>$count, 'questions'=>$output];
}

My Question is,

  1. Instead of doing it in code can we get the count array using sql query efficiently.
  2. If not, help me in optimizing the code.

T!A.


Source: sql

Leave a Reply