SQL QUERY问题 - 在不使用AVG（）的情况下查找订单的美元价值 [英] SQL QUERY Question -- Find Dollar value of an Order without using AVG()

问题描述

我正在尝试为订单获取平均美元价值并获取它，直到我添加下面应该给我的子查询< b> OrderItems的平均美元价值。我收到关键字''AS''附近的语法不正确。那是什么我做得不对？谢谢。



P.S.并非所有订单都在ORDERITEMS表中。

I am trying to get average dollar value for Orders and getting it until I add the subquery below which is supposed to give me average dollar value for OrderItems as well. I am getting "Incorrect syntax near the keyword ''AS''". What is that I am not doing right? Thank you.

P.S. not all the Orders are in the ORDERITEMS table.

SELECT COUNT(Orders.orderid) / SUM(Inventory.price) AS Orders_Average
FROM  ORDERITEMS LEFT OUTER JOIN
               Inventory ON OrderItems.partid = Inventory.partid RIGHT OUTER JOIN
               Orders ON OrderItems.orderid = Orders.orderid
               
WHERE NOT EXISTS 
(SELECT *   
FROM  ORDERITEMS
WHERE OrderItems.orderid <> Orders.orderid
GROUP BY ORDERITEMS.orderid
HAVING COUNT(ORDERITEMS.orderid) / SUM(Inventory.price) AS OrderItems_Average )

推荐答案

您不能在having子句中使用别名。您应该在最后一行AS OrderItems_Average中获取它。

you can not use alias in having clause. you should be getting it in the last line "AS OrderItems_Average".

HI，



将您的代码更改为：

Change your code to :

SELECT (COUNT(Orders.orderid) / SUM(Inventory.price)) AS Orders_Average
FROM  ORDERITEMS LEFT OUTER JOIN
               Inventory ON OrderItems.partid = Inventory.partid RIGHT OUTER JOIN
               Orders ON OrderItems.orderid = Orders.orderid
               
WHERE NOT EXISTS 
(SELECT *   
FROM  ORDERITEMS
WHERE OrderItems.orderid <> Orders.orderid
GROUP BY ORDERITEMS.orderid)

无需用别名来定义having子句。



谢谢

NO need to define the having clause with aliase name.

Thanks

这篇关于SQL QUERY问题 - 在不使用AVG（）的情况下查找订单的美元价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！