从订单行计算订单总和

发布于 2024-12-04 16:37:44 字数 217 浏览 0 评论 0原文

您好,我正在尝试计算订单的总和。 我收到了一个带有 ID 的订单,以及一些订单行。

Order
ID       OrderDate  CustomerName


OrderLine
OrderID  ProductId   Amount    Price

我想做一个选择语句,我得到以下输出。

ID、订单日期、客户名称、订单总计。

Hi I'm trying to calc the sum of an order.
I got an order with and Id, and a number of Orderlines.

Order
ID       OrderDate  CustomerName


OrderLine
OrderID  ProductId   Amount    Price

I would like to make a select statement, where I get the following output.

ID, OrderDate, CustomerName, OrderTotal.

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

扫码二维码加入Web技术交流群

发布评论

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

评论(2

空袭的梦i 2024-12-11 16:37:44

您需要 JOINGROUP BYSUM。像这样的东西:

SELECT o.ID,
       o.OrderDate,
       o.CustomerName,
       SUM(ol.Amount * ol.Price) AS OrderTotal
FROM   [Order] o
       LEFT JOIN OrderLine ol /*If not all orders may have items */
         ON o.ID = ol.OrderID
GROUP  BY o.ID,
          o.OrderDate,
          o.CustomerName  

You need a JOIN, GROUP BY and SUM. Something Like:

SELECT o.ID,
       o.OrderDate,
       o.CustomerName,
       SUM(ol.Amount * ol.Price) AS OrderTotal
FROM   [Order] o
       LEFT JOIN OrderLine ol /*If not all orders may have items */
         ON o.ID = ol.OrderID
GROUP  BY o.ID,
          o.OrderDate,
          o.CustomerName  
套路撩心 2024-12-11 16:37:44

你的问题不完整。但我会尝试猜测剩下的缺失部分。

假设您有两张表。一个表包含 Orders ,我们将其称为 tbl_orders ,另一个表包含订单详细信息,并将其称为 tbl_details

我就是这样做的

select a.ID,a.OrderDate,a.CustomerName, sum(b.Amount * b.Price) OrderTotal 
from tbl_orders a, tbl_details b where b.orderId = a.ID 
group by a.ID,a.OrderDate,a.CustomerName

Your question is incomplete. But I will try to guess the remaining missing parts.

Let's assume that you have two tables. One table has Orders , we will call it tbl_orders and another that has the details of the orders and will call it tbl_details.

here is how I would do it

select a.ID,a.OrderDate,a.CustomerName, sum(b.Amount * b.Price) OrderTotal 
from tbl_orders a, tbl_details b where b.orderId = a.ID 
group by a.ID,a.OrderDate,a.CustomerName
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文