SQL中的COUNT函数计算数据库中的整个数据

Question

I am trying to count the number of data in the database according to the category id and I have written the following lines of code:

public function getPodcastByCategoryId($catId){
    $args = array(
        'fields' => array(
                    'podcast.id', 
                    'podcast.title',  
                    'podcast.description', 
                    'podcast.duration',
                    'podcast.audio',
                    'podcast.image',
                    'podcast.category',
                    'podcast.added_date',
                    'categories.title AS category_title',
                    '(SELECT users.full_name FROM users WHERE id = podcast.added_by) as author',
                    '(SELECT COUNT(category) FROM podcast WHERE category = podcast.category) as episodes'
                ),
        'join'   => "LEFT JOIN categories on podcast.category = categories.id",
        'where' => array(
            'category' => $catId
        ),
    );
    return $this->select($args);
}

However, the function getPodcastByCategoryId($catId) is giving episodes = 3 which is the count of data present in table podcast.

Data in database:

My expected episodes would be 2 in category id 2 and 1 in category id 1.

Answer 1

for episode you should use COUNT(*)

$args = array(
      'fields' => array(
                  'podcast.id', 
                  'podcast.title',  
                  'podcast.description', 
                  'podcast.duration',
                  'podcast.audio',
                  'podcast.image',
                  'podcast.category',
                  'podcast.added_date',
                  'categories.title AS category_title',
                  '(SELECT users.full_name FROM users WHERE id = podcast.added_by) as author',
                  '(SELECT COUNT(*) FROM podcast WHERE categories.category = podcast.category) as episodes'
              ),
      'join'   => "LEFT JOIN categories on podcast.category = categories.id",
      'where' => array(
          'category' => $catId
      ),
  );