带有计算值条件的 SQL

发布于 2024-10-18 02:44:00 字数 265 浏览 2 评论 0原文

我有一张桌子，上面有产品、数量和价格。我需要选择每篇文章的平均价格在一定范围内的所有条目。到目前为止我的查询：

SELECT productid,AVG(SUM(price)/SUM(amount)) AS avg 
FROM stock WHERE avg>=$from AND avg<=$to GROUP BY productid

如果这样做，它告诉我 avg 不存在。另外，我显然需要分组，因为总和和平均值需要针对每种葡萄酒

原文

I have a table with products, their amount and their price. I need to select all entries where the average price per article is between a range.
My query so far:

SELECT productid,AVG(SUM(price)/SUM(amount)) AS avg 
FROM stock WHERE avg>=$from AND avg<=$to GROUP BY productid

If do this, it tells me avg doesn't exist.
Also I obviously need to group by because the sum and average need to be per wine

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

爱的那么颓废 2024-10-25 02:44:00

您需要将其放在 HAVING 子句中。您无法使用 WHERE 按聚合结果进行过滤。

MySQL 允许您在 HAVING 中引用列别名。您可能需要

SELECT 
      productid,
      AVG(price/amount) AS avg ,
      /*SUM(price)/SUM(amount) AS avg (<--- Or perhaps this?)*/
FROM stock 
GROUP BY productid
HAVING avg>=$from AND avg<=$to

但我不确定您到底想用 AVG(SUM(price)/SUM(amount)) 做什么，您可以显示一些示例数据吗？

You need to put it in the HAVING clause. You cannot filter by the result of aggregates using WHERE.

MySQL does allow you to reference column aliases in HAVING. You might need

SELECT 
      productid,
      AVG(price/amount) AS avg ,
      /*SUM(price)/SUM(amount) AS avg (<--- Or perhaps this?)*/
FROM stock 
GROUP BY productid
HAVING avg>=$from AND avg<=$to

But I'm not sure exactly what you are trying to do with AVG(SUM(price)/SUM(amount)) can you show some example data?

回复收藏 0 原文

~没有更多了~