douyan7916
2019-01-28 11:37
浏览 121
已采纳

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

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:

data in database

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

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douza9835 2019-01-28 11:43
    已采纳

    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
          ),
      );
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题