How to add discount and calculate total for each order id?

Question

Trying to add discount to the final result set, but not succeed.

And need total for each order id.

Here is the query:

declare @temp table (order_id int,product varchar(10),price int)
insert into @temp values
(1,'AA',100),(1,'AA',87),(1,'BB',56),(1,'BB',82),
(2,'AA',167),(2,'AA',43),(2,'BB',23),(2,'BB',78)
declare @discount table (product varchar(10),discount decimal(6,2))
insert into @discount values
('AA',0.9),('BB',0.85)

select order_id,t.product--,d.discount
      ,SUM(price * d.discount) as total
from @temp t join @discount d on t.product=d.product
group by order_id,t.product
order by order_id

Answer 1

Hi @Lylyy

add discount to the final result set

You need to add discount in the group by list too.

And need total for each order id.

You could use Grouping Sets.

Check this query:

select order_id,t.product,d.discount
      ,SUM(price * d.discount) as total
from @temp t join @discount d on t.product=d.product
group by grouping sets((order_id,t.product,d.discount),(order_id))
order by order_id

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.