CODEWITHSUNDEEP
mysqlsqlaverage
Tim M
Tim M

Reputation: 306

Query: SUM, AVG 3 days of data?

Here's my sqlFiddle - http://sqlfiddle.com/#!9/a6ed6a/3

I have a food log that holds a food item, total calories, date, and grams of protein, fat and carbohydrate.

I'm trying to calculate the percent of carbs, protein and fat consumed each day for 3 consecutive days for each user.

I'm retrieving the data with this query:

SELECT foodName, calories, carbs, fat, protein, logDate
FROM Students
LEFT JOIN FoodLog
ON Students.id=FoodLog.studentID  
WHERE FoodLog.studentID  != ''
AND logDate >= '2019-02-03' AND logDate <= '2019-02-05'
ORDER BY `FoodLog`.`logDate`  ASC

However, when I try retrieving the data with a percent, I only get one food item returned as in the following query:

SELECT foodName, calories, carbs, fat, protein, logDate,
((SUM(carbs)*4)/SUM(calories))*100 AS percentCarbs
FROM Students
LEFT JOIN FoodLog
ON Students.id=FoodLog.studentID  
WHERE FoodLog.studentID  != ''
AND logDate >= '2019-02-03' AND logDate <= '2019-02-05'
ORDER BY `FoodLog`.`logDate`  ASC

Any help will be much appreciated!

Thanks,

Tim

Upvotes: 0

Views: 136

Answers (1)

db702
db702

Reputation: 568

I believe you are just missing group by

SELECT foodName, calories, carbs, fat, protein, logDate,
((SUM(carbs)*4)/SUM(calories))*100 AS percentCarbs
FROM Students
LEFT JOIN FoodLog
ON Students.id=FoodLog.studentID  
WHERE FoodLog.studentID  != ''
AND logDate >= '2019-02-03' AND logDate <= '2019-02-05'
GROUP BY FoodLog.studentID, logDate
ORDER BY `FoodLog`.`logDate`  ASC

Upvotes: 2

Related Questions