mysql - 查询:SUM，AVG 3天数据？

这是我的sqlFiddle-http://sqlfiddle.com/#!9/a6ed6a/3

我有一个食物日志，其中包含食物，总卡路里，日期以及蛋白质，脂肪和碳水化合物的克数。

我正在尝试计算每个用户连续3天每天消耗的碳水化合物，蛋白质和脂肪的百分比。

我正在使用此查询检索数据：

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

但是，当我尝试使用百分比来检索数据时，仅返回一个食品，如以下查询所示：

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

任何帮助都感激不尽！

谢谢，

提姆

最佳答案

我相信你只是想念

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