MySQL with conditional fetch count record number

Reference Method Three:

Count Sub_type= ' Refund_fee ' records:

Method one. Select count (sub_type) from T where t.sub_type= ' refund_fee ';

Method two . Select sum (if (b.sub_type= ' Refund_fee ', 1,0)) from T;

Method Three. Select count (b.sub_type= ' refund_fee ' or null) from T;

Explain:

When select B.sub_type= ' Refund_fee ':

If Sub_type is Refund_fee, return 1,

is not empty and is not refund_fee otherwise returns 0,

Empty returns NULL.

So b.sub_type= ' refund_fee ' or null returns only Sub_type= ' Refund_fee ', and the rest returns NULL, and count (column name) is not counted as the number of NULL

Note: Count (*) will also count the number of NULL

Project SQL

    SELECT Subjectname,doctorname,count (1) as Sumnum,
   count (overtimes>0 or null) as Overnum            //Count overtimes>0 only

From Ht_personstream
WHERE 1=1
<if test= "subjectid!=" and subjectid!= ' null ' >
   and Subjectid = #{subjectid}
</if>
<if test= "starttime!=" and starttime!= ' null ' >
   date_format (ENDTIME, '%y-%m-%d ')>= #{starttime}]]>        //mysql date formatting
</if>
<if test= "endtime!=" and endtime!= ' null ' >
   <! [Cdata[and Date_format (ENDTIME, '%y-%m-%d ') <= #{endtime}]]>
</if>
GROUP by  Doctorid,subjectid

MySQL with conditional fetch count record number