根据小计和金额计算出总计

发布于 2024-12-05 14:45:29 字数 381 浏览 0 评论 0原文

我有一个包含购买订单数据的表。
每行包含购买的特定商品的数量、每件商品的成本和订单号组。购买的每个不同商品都是一个具有相同订单号的新行。

我基本上想退回该订单的总成本。我已经尝试过以下方法，但一无所获：

SELECT order_number, SUM( sub_total ) AS `total`
FROM
  SELECT order_number, SUM( SUM( amount ) * SUM( cost_per_item ) ) AS `sub_total`
    FROM `ecom_orders`
   WHERE member_id = '4'
GROUP BY order_number
ORDER BY purchase_date DESC

原文

I have a table with purchased orders data.
Each row contails the amount of certain item purchased, cost per item and the order number group. Each different item purchased is a new row with same order number.

I basically want to return the total cost for that order. I have tried the following but am getting nowhere:

SELECT order_number, SUM( sub_total ) AS `total`
FROM
  SELECT order_number, SUM( SUM( amount ) * SUM( cost_per_item ) ) AS `sub_total`
    FROM `ecom_orders`
   WHERE member_id = '4'
GROUP BY order_number
ORDER BY purchase_date DESC

分享到QQ

分享到微博

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

发布评论

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

浮生面具三千个 2024-12-12 14:45:29

几乎任何兼容 SQL-92 的 RDBMS 都会采用以下方式：

SELECT
order_number
,SUM(amount * cost_per_item) AS total
,purchase_date
FROM
ecom_orders
WHERE member_id = '4'
GROUP BY order_number,purchase_date
ORDER BY purchase_date DESC

Pretty much any SQL-92 compliant RDBMS will take this:

SELECT
order_number
,SUM(amount * cost_per_item) AS total
,purchase_date
FROM
ecom_orders
WHERE member_id = '4'
GROUP BY order_number,purchase_date
ORDER BY purchase_date DESC

回复收藏 0 原文

~没有更多了~