MySql count using and still show all data even using where clause

Question

Good Afternoon,

How i can show all data when using count and where clause in MySql. In my case, I have master data like pict at below.

and i using this query to show count the data.

SELECT body,
       count(body) 
from tb_list_data 
WHERE type ="FAC" 
AND  group by body 
order by body ASC

and then the result like pict at below

But i want the query result like pict in below.

how i do query to still show all data like that pict even using clause where?

Thank You.

Answer 1

By using WHERE type ="FAC" you are limiting yourself only on the body values where type equal FAC. By the way you have an additional AND , but I'm guessing it should be a typo.

You need to use a case condition .

Consider the following data ,

create table tb_list_data (
  type  varchar(10),
  body  varchar(10) );

insert into tb_list_data values 
('FAC','Hand'),('FAC','Head'),('MTC','Feet'),('MTC','Finger'),
('FAC','Hand'),('FAC','Head'),('FAC','Legs');

---

select body,
       count(case when type = 'FAC' then 1 end) as cnt
from tb_list_data
group by body
order by cnt asc  ;

Or written differently

select body,
       SUM(type = 'FAC') as cnt
from tb_list_data
group by body
order by cnt asc  ;

In both cases result would be

body    cnt
Feet     0
Finger   0
Legs     1
Hand     2
Head     2

See example