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.
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