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.
Category: +----+--------+--+ | id | name | | +----+--------+--+ | 1 | Type 1 | | | 2 | Type 2 | | | 3 | Type 3 | | +----+--------+--+
Questions +----+------+-------+ | id | name | catid | +----+------+-------+ | 1 | a | 1 | | 2 | b | 1 | | 3 | c | 1 | | 4 | d | 2 | | 5 | e | 2 | | 6 | f | 3 | +----+------+-------+
category_count = [3,2,1]
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,
T!A.
Depending on the DDL of your tables, your query should look like this:
SELECT c.name, COUNT(*)
FROM category c
JOIN questions q
ON q.catid = c.id
GROUP BY c.name
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments