douxi3404 2018-03-07 22:33
浏览 74
已采纳

使用where子句获取不同的行及其记录计数

-------------------------------------
Name of Student       |      class   
-------------------------------------
S1                            First
S11                           First
S12                           First
S13                           First
S2                            Second
S3                            Third
S31                           Third
-------------------------------------

Right now Im getting distinct classes with each of its count with below query

SELECT class,count(*) FROM students GROUP BY class

I get this, everything is good with this

array(
       "class"=> "First", "count"=>"4",
       "class"=> "Second", "count"=>"1",
       "class"=> "Third", "count"=>"2",
    ) 

but I want to apply where condition only for the counts, so I want all distinct classes, with counts as per where clause

For example if my condition is class="First"

SELECT class,count(*) FROM students where class="First" GROUP BY class

that query gives me this

array(
       "class"=> "First", "count"=>"4"
    )

but I want all distinct classes, so my expected result is the following

array(
       "class"=> "First", "count"=>"4",
       "class"=> "Second", "count"=>"0",
       "class"=> "Third", "count"=>"0",
    )  

any help is appreciated, thanks

  • 写回答

3条回答 默认 最新

  • duanchongzi9997 2018-03-07 22:39
    关注

    Use SUM() to count the number of rows that match the desired condition.

    SELECT class, SUM(class = "First") AS count
    FROM students
    GROUP BY class
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?