1

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.

2
  • Please for other questions consider using text instead of image as I have shown in my answer Commented Sep 27, 2023 at 8:38
  • Please consider this - images are a "bad thing" (TM)! I can't see them on the bus!
    – Vérace
    Commented Sep 27, 2023 at 15:17

1 Answer 1

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

1
  • Hi Ergest Basha, oh Great, thank you so much. work nicely.
    – epm.007
    Commented Sep 27, 2023 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.