CODEWITHSUNDEEP
mysql
picolo
picolo

Reputation: 41

How to have three counts in one sql query and will display 3 separated results in mySQL?

I have two tables. My first table is animal_tbl the second one is animal_ate_tbl and food_type_tbl.

this is the visualization of animal_tbl:

enter image description here

animal_ate_tbl:

enter image description here

food_type_tbl

enter image description here

I want a query that will select the: animal_num, animal_fname+animal_lname, count of banana she ate and count of apple she ate and the count of chico she ate. I created my own query however it is outputing the wrong result. can you help me? I promise to vote up the one who can help me :)) this is the sample query i got but is wrong:

select
    a.animal_num,
    a.animal_fname,
    a.animal_lname, 
    (
        SELECT
            COUNT(*)
        FROM
            animal_ate_tbl
        WHERE
            food_id = x22
        group by
            animal_id
    ) as banana, 
    (
        SELECT
            COUNT(*)
        FROM
            animal_ate_tbl
        WHERE
            food_id = x33
        group by
            animal_id
    ) as banana, 
    (
        SELECT
            COUNT(*)
        FROM
            animal_ate_tbl
        WHERE
            food_id = x44
        group by
            animal_id
    ) as chico
from
    animal_tbl as a,
    animal_ate_tbl
group by
     a.animal_num = animal_id

However, this should be the output:

enter image description here

I will promise that i will give an upvote to those who will answer. btw the table is only a sample table that im doing.

Upvotes: 0

Views: 28

Answers (1)

cdaiga
cdaiga

Reputation: 4939

Try this instead:

SELECT 
   A.animal_num, 
   CONCAT(IFNULL(A.animal_fname,''),' ', IFNULL(A.animal_lname,'')) fullName,
   (SUM(IF(C.food_type='banana',1,0))) banana,
   (SUM(IF(C.food_type='apple',1,0))) apple,
   (SUM(IF(C.food_type='chico',1,0))) chico
FROM animal_tbl A LEFT JOIN animal_ate_tbl B
ON A.animal_nu=B.animal_id LEFT JOIN food_type_tbl C
ON B.food_id=C.food_id
GROUP BY A.animal_num, 
         CONCAT(IFNULL(A.animal_fname,''),' ', IFNULL(A.animal_lname,''));

Upvotes: 1

Related Questions