Kenny Lajara Kenny Lajara - 7 months ago 25
SQL Question

Left Join + count + sum on MySQL

This question is similar to my previous question. But with several variants (I have probems with advanced JOINs and 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

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,
  SUM(f.category='fruit')  AS fruits,
  SUM(f.category='fast')   AS fast,
  SUM(f.category='cereal') 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/15

Search the web or SO for to find more examples.