0

Ok, first of all, I am going to change the name of the tables, fields and values, I am just keeping the structure of my data base for you to understand.

Now, let's suppouse I have a table people, with the fields, id, age, country and language. And another table foods with the food each person in table people ate, fields: person_id, food, units. Note: food is char, not the ID of another table and I can't change this structure.

Now, let's put some data:

.

People

ID | AGE | COUNTRY | LANGAGE
1  |  25 |   usa   |  en
2  |  46 |   mex   |  es

.

Foods

ID | PERSON_ID | FOOD  | UNITS
1  |     1     | apple |   2
2  |     1     | grape |  24
3  |     1     | pizza |   1
4  |     2     | apple |   3
5  |     2     | pizza |   2

.

Fine, what I need is to get the id, age, country and the sum of the apple and grape eaten by a person (ignoring pizza). In this example, if I look for ID 1, I should get:

ID | AGE | COUNTRY | UNITS 
1  |  25 |   usa   | 26

And for ID 2:

ID | AGE | COUNTRY | UNITS 
2  |  46 |   mex   | 3
Just a nice guy
  • 549
  • 3
  • 19
  • 1
    Based on the data available, shouldn't the units be 26 and 3 for person 1 and 2 respectively? – Edu Apr 29 '16 at 14:15

5 Answers5

1
select p.id, p.age, p.country, sum(f.units) as units
from people p join foods f
on (p.id = foods.person_id and f.food in ('apple', 'grape'))
group by p.id, p.age, p.country

If you need other combinations of foods, just edit the content of the in clause.

Luca Calabrese
  • 126
  • 1
  • 4
1

http://sqlfiddle.com/#!9/38304/2

SELECT p.*, SUM(f.units)
FROM people p
LEFT JOIN foods f
ON f.person_id = p.id
  AND f.food in('apple','grape')
GROUP BY p.id
Alex
  • 16,739
  • 1
  • 28
  • 51
0
SELECT p.Id, p.Age,p.country, sum(f.units)
FROM people AS p
INNER JOIN foods AS f ON f.person_id = p.id
WHERE f.food <> 'Pizza'
GROUP BY p.id,p.age,p.country
gh9
  • 10,169
  • 10
  • 63
  • 96
  • Well, I have a lot of different `food`, so, I'll try the `WHERE f.food = 'apple' OR f.food = 'grape'` instead of `WHERE f.food <> 'Pizza'`, also, it needs to be a `left join` not an `inner` but I was not specific on the body of my message. Anyway, I got your idea, the trick is on the GROUP BY. I'll try and come back. – Just a nice guy Apr 29 '16 at 14:21
  • no worries you can easily change it to left join, I would change the where clause to be `WHERE f.food IN ('apple','grape')` that way you dont have to keep adding on `OR` statements – gh9 Apr 29 '16 at 14:27
  • Although this code may answer the question, providing additional context regarding _why_ and/or _how_ it answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight Apr 29 '16 at 14:53
0
select p.id, p.age, p.country, sum(f.units) as units 
from people p 
join foods f on f.person_id = p.id 
where f.food <> 'Pizza' 
group by p.id, f.food
Rafael Mota
  • 127
  • 4
  • are you sure about `f.food`? – Alex Apr 29 '16 at 14:43
  • Although this code may answer the question, providing additional context regarding _why_ and/or _how_ it answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight Apr 29 '16 at 14:54
  • do you need the total consumption of food per person, or the total food consumed per person? – Rafael Mota Apr 29 '16 at 14:58
0
select 
t1.id, t1.age, t1.country, sum(t2.units)
from people as t1, foods as t2
where 
t1.id = t2.person_id and t2.food != 'pizza'
group by t1.id 
order by t1.id
felix
  • 16
  • 3