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.

Master Data

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

result from the query

But i want the query result like pict in below.

enter image description here

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

Thank You.

• Ergest Basha: Please for other questions consider using text instead of image as I have shown in my answer
• Vérace: Please consider this - images are a "bad thing" (TM)! I can't see them on the bus!

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

• epm.007: Hi Ergest Basha, oh Great, thank you so much. work nicely.