下面是我的资料
秩序
ID state
1 2
2 1订单项
orderID item qty price
1 A 1 10
1 B 3 20
2 A 2 10订单状态
stateID name
1 new
2 complete我想要的风景
Id state total_qty Total_price
1 complete 4 70
2 new 2 20我的问题到目前为止
SELECT distinct O.Id, OS.name, SUM(OI.qty) as total_qty, SUM(OI.qty*OI.price) as Total_price
FROM Order O
join OrderItem OI ON O.ID = OI.ordeID
join OrderState OS ON O.state = OS.StateID
GROUP BY O.ID它关于分组的保留错误不能想办法绕过它感谢帮助
1条答案
按热度按时间ijxebb2r1#
您需要按所选的所有列进行分组，而不是使用以下函数进行聚合
sum()```SELECT o.id,
os.name as state_name,
SUM(oi.qty) as total_qty,
SUM(oi.qty * oi.price) as total_price
FROM
orderoJOIN OrderItem oi ON o.id = oi.orderID
JOIN OrderState os ON o.state = os.StateID
GROUP BY o.id, os.name