Sum a group of records if one of them equals a specific value – Databases – SitePoint Forums

Using MySQL, I have two tables, one for meals and the second for food items in the meals, linked by foodid which keys the food table and is a field in the meals table. The meals table has fields for trackdate and mealcode to indicate which meal it is. So if all I wanted was a list of meals that contain an ingredient I could use:

select mealcodeid, date(trackdate)
	from meals
	where foodid = 106;

However, I would like to join the food table to this and be able to sum the value in the food table, calories for example, of all the foods that were included in that meal.

I tried:

select m1.mealcodeid, date(m1.trackdate) as datedly, sum(d2.calories) as totcals
	from meals as m1
	Inner join foods as d2 on d2.foodid = m1.foodid
	Group by date(m1.trackdate), m1.mealcodeid
	having m1.foodid = 106
	order by date(m1.trackdate), mealcodeid;

This returns no records which is incorrect. How do I get a group of records to sum if at least one of the records has a foodid of a particular value?

Maybe it is a better idea to split your data into three tables to get rid of the trackdate column and be able to use the food for more then one meal.
for example an onion will be used in many meals. So why store it multiple times in a table. Just store it ones and store the amount needed in the meal in another table.

I have made a small fiddle. Of course this can also be improved because you normally not only need an amount of food but also a unit it is measured in. But i guess it’s a good start for you

this is gold –

create table incredients
(
  mealid int,
  foodid int,
  amount int,
  primary key(mealid, foodid)
);

thank you for not declaring an auto_increment PK here

First, thanks for the input, I do appreciate it. And great job on SQL Fiddle, love it!

Second, you have to have the date in there. I am tracking individual meals so lunch yesterday is different than lunch today. Mealid to me is 1 = breakfast, 2 = lunch, etc.

And also I want to be able to add an ingredient more than once to a meal. So truth is there is no unique key to this table so I use a generated key.

Anyway, I solved this problem with a Temporary Table and it works great.

But let me ask the question in a more general sense. In MySQL, can I create a query that will include a group in the result if at least one record in the group meets a specific criteria for a field (non-group by field, non-aggregate field)? So if one record meets the criteria the whole group is included and if none of the records meets the criteria the whole group will be excluded.

Again, thanks for the response.

could you give an example please

yes, this can be done – count the number of items in the group that meet the criterion, and use COUNT

yes, this can be done – count the number of items in the group that meet the criterion, and use COUNT

> 0 in the HAVING clause

Great thanks, I’ll try that.

August 28, 2022, 9:41pm

#8

you actually have a 3-level hierarchy (meal, entree, ingred), not 2-level (meal, ingred)