mysql - 编写查询以获取两列相乘的总和

Question

我编写了查询来获取两列的乘法之和。

select *,sum(op.qty*op.price) as total from `order` as o 
join order_products as op on op.order_id = o.order_id
where o.user_id = 5 and o.order_id = 10 group by op.order_product_id

我的输出看起来像

在这里，如图所示，我想要所有记录以及总数。

那么，我应该做什么？

Answer 1

我已将查询更改为

select
t1.*,
t2.total
FROM
(
  SELECT op.*
  FROM `order` AS o JOIN order_products AS op ON op.order_id = o.order_id
  WHERE o.user_id = 5 AND o.order_id = 10
) as t1
LEFT JOIN
(
  SELECT sum(op.qty * op.price) as total
  FROM `order` AS o JOIN order_products AS op ON op.order_id = o.order_id
  WHERE o.user_id = 5 AND o.order_id = 10
) as t2
ON 1 = 1

它给出的结果是这样的

这个查询运行完美。