时间戳

首页 > 解决方案 > Mysql SELECT 查询 group by 与 sum 和 avg

mysql - Mysql SELECT 查询 group by 与 sum 和 avg

问题描述

如果我添加了“按日期分组”，则 sum 或 avg 函数不起作用。

这是一张桌子

| date         | calories |
|-------------------------|
| 2021-03-28   | 42.50    |
| 2021-03-30   | 500.00   |
| 2021-03-31   | 35.00    |
| 2021-04-01   | 200.00   |
| 2021-04-01   | 35.00    |

这是创建查询

SELECT CONCAT(round(IF(avg(up.calories), avg(up.calories), 0), 2), "kcal") as avg, CONCAT(round(IF(SUM(up.calories), SUM(up.calories), 0), 2), "kcal") as total_burned
FROM `tbl` as `up`
WHERE `date` BETWEEN "2021-03-28" AND "2021-04-03"
AND `calories` != '0'
GROUP BY `date`

以下是我的查询结果

| avg          | total_burned |
|-----------------------------|
| 42.50        | 42.50        |
| 500.00       | 500.00       |
| 35.00        | 35.00        |
| 235.00       | 235.00       |

但实际上，我想要这种类型的结果

| avg          | total_burned |
|-----------------------------|
| 203.13       | 812.50       |

标签： mysql

解决方案

自己滚

DROP TABLE IF EXISTS T;
create table t( date date, calories decimal(10,2));
insert into t values
( '2021-03-28'   , 42.50    ),
( '2021-03-30'   , 500.00   ),
( '2021-03-31'   , 35.00    ),
( '2021-04-01'   , 200.00   ),
( '2021-04-01'   , 35.00    );

select sum(calories) sumcal,sum(calories) / count(distinct date) calcavg, avg(calories)
from t;

+--------+------------+---------------+
| sumcal | calcavg    | avg(calories) |
+--------+------------+---------------+
| 812.50 | 203.125000 |    162.500000 |
+--------+------------+---------------+
1 row in set (0.002 sec)

推荐阅读