连接两个表的SQL订单值总和

由 ’听天由命发布于 2020-05-09 15:26:21

我想从另一个表中获取每个订单的销售总额以及客户和员工详细信息

订单明细表

OrderID ProductID   UnitPrice   Quantity    Discount
10248   11              10         1              0
10248   42              20         2              0
10248   72              20         3              0
10249   14              10         1              0
10249   51              40         2              0

订单表

OrderID CustomerName    EmployeeName
10248   C1               E1
10249   C2               E2

所需结果

orderid sales   Customer   Employee
10248   110        C1        E1
10249    90        C2        E2

所以我需要每个订单的总销售额（例如10248订单的销售额为10 * 1 + 20 * 2 + 20 * 3 = 10 + 40 + 60 = 110）以及相应的员工姓名和客户姓名

查询已尝试

select od.orderid, Unitprice * Quantity as sales 
group by od.orderid
From [Order Details] od
inner join orders o on od.orderid = o.orderid

错误：

关键字“从”附近的语法不正确。

负能量

2020-05-09 15:26:21

FROM belongs before GROUP BY.

select od.orderid, unitprice * quantity as sales 
from [Order Details] od
group by od.orderid;

加入订单信息：

select o.orderid, od.sales, o.customername, o.employeename
from orders o
join
(
  select orderid, unitprice * quantity as sales
  from [Order Details]
  group by orderid
) od on od.orderid = o.orderid
order by o.orderid;

望_

2020-05-09 15:26:21

您需要按订单进行汇总，然后取每个订单的销售总额：

SELECT
    od.orderid,
    SUM(Unitprice * Quantity) AS sales 
FROM [Order Details] od
INNER JOIN orders o
    ON od.orderid = o.orderid
GROUP BY
    od.orderid;

回忆那么真

2020-05-09 15:26:21

Try the following, you are having a group by before from. Also you do not need to do group by if you are using no aggregation instead you can use distinct.

select 
  od.orderid, 
  sum(Unitprice * Quantity) as sales 
From [Order Details] od    
inner join orders o on od.orderid=o.orderid 
group by
   od.orderid

’听天由命

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3229
注册排名
2376