MySQL syntax for count

Posted on 2011-09-28
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

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?

Question by:cpeters5
  • 2
LVL 41

Expert Comment

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

Author Comment

ID: 36717121
Thanks ralmada
LVL 41

Accepted Solution

ralmada earned 500 total points
ID: 36717126

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
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 …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now