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

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

you could do something like this:

SELECT class
  ,count(case when class='first' then 1 else null end) 
FROM students 
GROUP BY class

SELECT class, IF(class='first',count(1),0)
FROM students 
GROUP BY class

MySQL has an IF() function that takes three arguments, a test, the result if true and the result if false. Since you only want to count if class is 'first' you can return 0 if the test fails and return the count (I prefer to count 1 when simply counting rows) if the test passes.

In MsSQL the same function is IIF().