从 SQL 表计算每日平均销售额

Question

我想从我的sql表中计算每日平均销售额。我尝试使用以下查询，但它给出错误，说"关键字选择附近的语法不正确">

select avg(select sum(total) from canteen group by date) as average from canteen

Answer 1

不要嵌套聚合函数。 这是您希望获得每个日期的总和：

select day, sum(total)
from canteen
group by day;

您可以使用子查询获取平均值：

select avg(total)
from (select day, sum(total) as total
      from canteen
      group by day
     ) c;

或者，一个更简单的替代方案：

select sum(total) / count(distinct day)
from canteen;

Answer 2

最好尝试

使用这样的子查询：

select avg(total) from(select sum(total) from canteen group by date)

Answer 3

如果您不为嵌套查询而烦恼，我更喜欢;

select avg(total) from(select sum(total) as total from canteen group by date) as T

Answer 4

正确的语法是

SELECT date, avg(total) average FROM (
SELECT date, sum(total)total FROM canteen GROUP BY DATE
)AS A
grouP BY date