CODEWITHSUNDEEP
mysqlsqlselect
Afsar
Afsar

Reputation: 3124

Count Distinct Values in column , Return Zero if not found On Where Clause

This is my Table:

This is my table

and I am running this query,

SELECT fruits,COUNT(*) as count FROM meva where owner = 'bill' GROUP BY fruits ORDER BY count

and i am getting result as

enter image description here

Is it possible to get the other fruits items as 0 if not matched with owner like this,

Fruit   | count
Apple   ,   2
Mango   ,   0
Banana  ,   0
Strawberry ,0
Appricot,   0
Alfonso ,   0

A little modification in my query is most appreciated. thanks

Upvotes: 0

Views: 2224

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

Yes there's a nice answer up there. You can also try this!

SELECT  fruits, 
        SUM(Coalesce(owner='bill',0)) as counts
FROM     meva 
GROUP BY fruits 
ORDER BY fruits
;

Upvotes: 1

John Woo
John Woo

Reputation: 263713

use SUM() and CASE

SELECT  fruits, 
        SUM(CASE WHEN owner = 'bill' THEN 1 ELSE 0 END) as `count `
FROM     meva 
GROUP BY fruits 
ORDER BY fruits

or by using IF (in MySQL only)

SELECT  fruits, 
        SUM(IF(owner = 'bill',1,0)) as `count `
FROM     meva 
GROUP BY fruits 
ORDER BY fruits

Upvotes: 1

Related Questions