MySQL - how to write a query with multiple SELECTs?

Question

MySQL

Let's say there is a table TBL1

box_name	fruit_name
box1	apple
box1	orange
box2	apple
box1	tomato
box2	tomato

 
As a result, it should turn out like this:

box_name	total_fruit	fruit_apple	fruit_orange	fruit_tomato
box1	3	one	one	one
box2	2	one	0	one

 
Actually how to get the first two columns figured out, but how to add the rest?

Answer 1

SELECT
    box_name,  
    SUM(CASE WHEN (fruit_name='apple') THEN 1 ELSE 0 END) AS fruit_apple,
    SUM(CASE WHEN (fruit_name='orange') THEN 1 ELSE 0 END) AS fruit_orange,
    .... и тд
FROM 
    your_table
GROUP BY 
    box_name

Answer 2

It looks like a topic on grouping data, you just need to group and calculate how much of what
total_fruit will probably have to be considered as a subquery either at the php level or whatever you have there