从订单行计算订单总和

发布于 2024-12-04 16:37:44 字数 217 浏览 4 评论 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.

分享到QQ

分享到微博

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

发布评论

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

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

您需要 JOIN、GROUP BY 和 SUM。像这样的东西：

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

回复收藏 0 原文

套路撩心 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

回复收藏 0 原文

~没有更多了~