Solved

MySQL syntax for count

Posted on 2011-09-28
3
247 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:cpeters5
  • 2
3 Comments
 
LVL 41

Expert Comment

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

Author Comment

by:cpeters5
ID: 36717121
Perfect!
Thanks ralmada
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 36717126
or

select col1, sum(if(col2 > 0,1,0))
from tab group by col1
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question