<div>
select col1, sum(case when col2 &gt;&nbsp;0 then 1 else 0 end) <br />
from tab group by col1
</div>


select col1, sum(case when col2 > 0 then 1 else 0 end)
from tab group by col1

<div>
Perfect!<br />
Thanks ralmada
</div>


<div>
<div class="content wysiwyg-content">
I want to count records in a table that satisfying a condition. &nbsp;But I also want to output a zero if there is no such record. &nbsp;For example<br />
<br />
Tab=<br />
id &nbsp; col1 &nbsp; &nbsp;col2 &nbsp; &nbsp;<br />
1 &nbsp; &nbsp;a &nbsp; &nbsp; &nbsp; &nbsp;5<br />
2 &nbsp; &nbsp;b &nbsp; &nbsp; &nbsp; &nbsp;0<br />
3 &nbsp; &nbsp;c &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; <br />
4 &nbsp; &nbsp;a &nbsp; &nbsp; &nbsp; 12<br />
<br />
The query<br />
select col1, count(*) from tab where col2 &gt;&nbsp;0 group by 1;<br />
would return <br />
a &nbsp;2<br />
c &nbsp;1<br />
<br />
I want the put put to also show<br />
b &nbsp; 0<br />
<br />
How do I do this?<br />
pax<br />
<br />

</div>
</div>


I want to count records in a table that satisfying a condition.  But I also want to output a zero if there is no such record.  For example

Tab=
id   col1    col2    
1    a        5
2    b        0
3    c        2         
4    a       12

The query
select col1, count(*) from tab where col2 > 0 group by 1;
would return 
a  2
c  1

I want the put put to also show
b   0

How do I do this?
pax



MySQL syntax for count

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.