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

Question

-------------------------------------
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

Answer 1

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