Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL syntax for count

Posted on 2011-09-28
3
Medium Priority
?
256 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 2000 total points
ID: 36717126
or

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

824 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