Left Join + count + sum on MySQL

Question

This question is similar to my previous question. But with several variants (I have probems with advanced JOINs and I couldn't find any usefull info in the forum).

Again, I changed the name of the tables, fields and values, keeping just the structure of my data base for you to understand.

Now, let's suppouse I have this (and I can't change the structure):

.

People

ID | AGE | COUNTRY 
1  |  25 |   usa   
2  |  46 |   mex   

...

.

Foods

ID | PERSON_ID | CATEGORY | FOOD       | UNITS
1  |     1     | fruit    | apple      |   2
2  |     1     | fruit    | grape      |  24
3  |     1     | fruit    | orange     |   5
3  |     1     | fast     | pizza      |   1
4  |     1     | fast     | hamburguer |   3
5  |     1     | cereal   | corn       |   2

...

.

But I have hundreds of people all with their relation in table foods, about eight categories on foods and each category has 4 to 24 food.

Fine, currently I am using a code similar to this one:

SELECT p.*, SUM(f.units) as orapple
FROM people p
LEFT JOIN foods f
ON f.person_id = p.id
  AND f.food in('apple','orange')
WHERE p.id = 1
GROUP BY p.id

To get this:

ID | AGE | COUNTRY | ORAPPLE
1  |  25 |   usa   |    7

Note that orapple in the result is the sum of the numbers on units, specifically, where food is equal to 'orange' and 'apple'.

Now, what I need it to add the number of each category, example, I need this:

ID | AGE | COUNTRY | ORAPPLE | FRUIT | FAST | CEREAL
1  |  25 |   usa   |    7    |   3   |  2   |   1   

Answer 1

Use the result from

SELECT DISTINCT category FROM foods;

to construct the following query:

SELECT p.*,
  SUM(CASE WHEN f.food in ('apple','orange') THEN f.units ELSE 0 END) as orapple,
  COUNT(f.category='fruit'  OR NULL) AS fruits,
  COUNT(f.category='fast'   OR NULL) AS fast,
  COUNT(f.category='cereal' OR NULL) AS cereal 
FROM people p
LEFT JOIN foods f
ON f.person_id = p.id
WHERE p.id = 1
GROUP BY p.id;

http://sqlfiddle.com/#!9/71e12/21

Search the web or SO for pivot-table to find more examples.