我有下面的表格
MySQL [distributor]> select * from orderitems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | BR01 | 100 | 5.49 |
| 20005 | 2 | BR03 | 100 | 10.99 |
| 20006 | 1 | BR01 | 20 | 5.99 |
| 20006 | 2 | BR02 | 10 | 8.99 |
| 20006 | 3 | BR03 | 10 | 11.99 |
| 20007 | 1 | BR03 | 50 | 11.49 |
| 20007 | 2 | BNBG01 | 100 | 2.99 |
| 20007 | 3 | BNBG02 | 100 | 2.99 |
| 20007 | 4 | BNBG03 | 100 | 2.99 |
| 20007 | 5 | RGAN01 | 50 | 4.49 |
| 20008 | 1 | RGAN01 | 5 | 4.99 |
| 20008 | 2 | BR03 | 5 | 11.99 |
| 20008 | 3 | BNBG01 | 10 | 3.49 |
| 20008 | 4 | BNBG02 | 10 | 3.49 |
| 20008 | 5 | BNBG03 | 10 | 3.49 |
| 20009 | 1 | BNBG01 | 250 | 2.49 |
| 20009 | 2 | BNBG02 | 250 | 2.49 |
| 20009 | 3 | BNBG03 | 250 | 2.49 |
+-----------+------------+---------+----------+------------+
18 rows in set (0.098 sec)我可以将指定订单的总价总结为;
MySQL [distributor]> select order_num, sum(quantity*item_price)
as total_price from orderitems where order_num = 20008;
+-----------+-------------+
| order_num | total_price |
+-----------+-------------+
| 20008 | 189.60 |
+-----------+-------------+
1 row in set (0.026 sec)如何聚合每个订单的总价并将它们全部显示在一起?
发布于 2018-08-12 14:20:41
一种SELECT语句子句,用于将查询结果划分为多个行组,通常用于对每个组执行一个或多个聚合。SELECT语句为每个组返回一行。
所以在你所有的东西都很好,只需要通过order_num分组
select order_num, sum(quantity*item_price) as total_price from orderitems
group by order_num发布于 2018-08-12 14:16:22
使用分组依据:
select order_num, sum(quantity*item_price) as total_price
from orderitems group by order_num发布于 2018-08-12 14:18:10
如果您想显示所有订单的总价,您可以从select中删除订单编号:-
select sum(quantity*item_price) as total_price from orderitems;但是,如果您希望显示每个订单的所有订单的总价,您可以使用以下查询:-
select order_num, sum(sum(quantity*item_price)) OVER() as total_price
from orderitems
group by order_num;https://stackoverflow.com/questions/51806232
复制相似问题