Solved

MySQL syntax for count

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
[MYSQL]: Delete is very slow 4 91
Parts and Products table schema in mysql 6 59
MySql Recovery 2 28
How to extract database info from current month and year 7 12
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 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