• ### Question

• Hi,

we have following tables:

Products (prodId, price)

OrderDetails (orderId, orderDetailsId, quantity, prodId).

In order to calculate the total amount for all orders i would write:

select orderId, (od.quantity*p.price) as orderTot

from OrderDetails OD, Products P

where p.prodId=OD.prodId

group by orderId

In order to find the most expensive order we can run MAX() on it:

select max(T.orderTot) from

(

select orderId, (od.quantity*p.price) as orderTot

from OrderDetails OD, Products P

where p.prodId=OD.prodId

group by orderId

) T

My Question is - how do we present also the OrderId of the max order (most expensive order)?

Thank you.

Monday, May 20, 2019 12:26 PM

• Hi Octopus01,

To get total amount for all orders as well as most expensive order, please try the following:

```DECLARE @Products TABLE (productId INT, price MONEY);
INSERT INTO @Products (productId, Price)
VALUES (1, 10.00)
, (2, 20.00)
, (3, 30.00)
, (4, 40.00)
, (5, 50.00)
, (6, 60.00)
, (7, 70.00)
, (8, 80.00)
, (9, 90.00)
, (10, 100.00)
, (11, 110.00)
, (12, 120.00);

DECLARE @OrderDetails TABLE (orderId INT
, orderDetailsId INT, quantity INT,  productId INT);
INSERT INTO @OrderDetails (orderId, orderDetailsId, quantity, productid)
VALUES (1, 1, 5, 10)
, (1, 2, 7, 3)
, (1, 3, 2, 12)
,( 2, 1, 5, 8)
, (2, 2, 7, 11);

-- total amount for all orders
SELECT COALESCE(CAST(a.orderID AS VARCHAR), 'Total:') AS orderID
, SUM(a.quantity * b.price) AS totalSum
FROM @OrderDetails AS a INNER JOIN
@Products AS b ON a.productId = b.productId
GROUP BY GROUPING SETS ((orderID), ())
ORDER by totalSum;

;WITH rs AS
(
SELECT a.orderID
, SUM(a.quantity * b.price) AS totalSum
FROM @OrderDetails AS a INNER JOIN
@Products AS b ON a.productId = b.productId
GROUP BY GROUPING SETS ((orderID), ())
)
SELECT TOP(1) *
FROM rs
WHERE OrderID IS NOT NULL
ORDER by totalSum DESC;```

Output:
```orderID	totalSum
2	1170.00```
Tuesday, May 21, 2019 10:30 PM
• Thank you all,

Mona Lv, DIEGOCTN

There is some kind of a confusion here which is my fault. I really apology, i made a mistake in the code i wrote in my question.

Every order in orderDetails has several rows.

example:

orderId   orderDetailsId    quantiry   productid

1                1                     5              10

1                2                      7             3

1                3                      2              12

2                1                      5              8

2                2                      7              11

and so on. In order to get the order total per order we must sum (os.quantity*p.price) after grouping by orderId.

only once we have the sum for every order (the total per order) we can look for the max.  Now, once we have the max total (not the max row of quantity*price ) we need to add the order id to it.

The code i meant to write is:

select max(T.orderTot) from

(

select orderId, SUM(od.quantity*p.price) as orderTot

from OrderDetails OD, Products P

where p.prodId=OD.prodId

group by orderId

) T

The question is, how do i add the order id to the max total?

Soumen - having (od.quantity*p.price) = max(od.quantity*p.price) - this is not legal code.

ok thanks for the explanation. Here you go:

select orderId, sum(od.quantity*p.price) orderTot
from OrderDetails OD, Products P
where p.prodId=OD.prodId
group by orderId
having sum(od.quantity*p.price) = (select max(orderTot) from (select sum(od.quantity*p.price) orderTot
from OrderDetails OD, Products P
where p.prodId=OD.prodId
group by orderId) T

)

Plz mark as answer if it solves the problem. Thanks.

• Edited by Wednesday, May 22, 2019 4:18 AM
• Marked as answer by Wednesday, May 22, 2019 8:35 AM
Wednesday, May 22, 2019 4:12 AM

### All replies

• ```with Ct as (
select orderId, (od.quantity*p.price) as orderTot, row_number() OVER ( order by (od.quantity*p.price) desc ) as rn

from #OrderDetails od inner join #Products p

on p.prodId=od.prodId

)
select orderId, orderTot from Ct where rn=1```
Monday, May 20, 2019 12:49 PM
• Hi Octopus01,

According to my test, the following code will be helpful.

```select Top(1) orderId, (OD.quantity*P.price) as orderTot
from OrderDetails OD inner join Products P
on P.prodId=OD.prodId
order by orderTot desc
go ```

Best Regards,

Mona Lv

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

• Proposed as answer by Wednesday, May 22, 2019 8:55 AM
Tuesday, May 21, 2019 7:07 AM
• Hi,

select orderId, orderTot
(
select orderId, max(od.quantity*p.price) as orderTot
from OrderDetails OD, Products P
where p.prodId=OD.prodId
group by orderId
having (od.quantity*p.price) = max(od.quantity*p.price)
) T

Mark as answer if it works. Thanks.

Tuesday, May 21, 2019 7:19 AM
• Thank you all,

Mona Lv, DIEGOCTN

There is some kind of a confusion here which is my fault. I really apology, i made a mistake in the code i wrote in my question.

Every order in orderDetails has several rows.

example:

orderId   orderDetailsId    quantiry   productid

1                1                     5              10

1                2                      7             3

1                3                      2              12

2                1                      5              8

2                2                      7              11

and so on. In order to get the order total per order we must sum (os.quantity*p.price) after grouping by orderId.

only once we have the sum for every order (the total per order) we can look for the max.  Now, once we have the max total (not the max row of quantity*price ) we need to add the order id to it.

The code i meant to write is:

select max(T.orderTot) from

(

select orderId, SUM(od.quantity*p.price) as orderTot

from OrderDetails OD, Products P

where p.prodId=OD.prodId

group by orderId

) T

The question is, how do i add the order id to the max total?

Soumen - having (od.quantity*p.price) = max(od.quantity*p.price) - this is not legal code.

Tuesday, May 21, 2019 9:28 PM
• Hi Octopus01,

To get total amount for all orders as well as most expensive order, please try the following:

```DECLARE @Products TABLE (productId INT, price MONEY);
INSERT INTO @Products (productId, Price)
VALUES (1, 10.00)
, (2, 20.00)
, (3, 30.00)
, (4, 40.00)
, (5, 50.00)
, (6, 60.00)
, (7, 70.00)
, (8, 80.00)
, (9, 90.00)
, (10, 100.00)
, (11, 110.00)
, (12, 120.00);

DECLARE @OrderDetails TABLE (orderId INT
, orderDetailsId INT, quantity INT,  productId INT);
INSERT INTO @OrderDetails (orderId, orderDetailsId, quantity, productid)
VALUES (1, 1, 5, 10)
, (1, 2, 7, 3)
, (1, 3, 2, 12)
,( 2, 1, 5, 8)
, (2, 2, 7, 11);

-- total amount for all orders
SELECT COALESCE(CAST(a.orderID AS VARCHAR), 'Total:') AS orderID
, SUM(a.quantity * b.price) AS totalSum
FROM @OrderDetails AS a INNER JOIN
@Products AS b ON a.productId = b.productId
GROUP BY GROUPING SETS ((orderID), ())
ORDER by totalSum;

;WITH rs AS
(
SELECT a.orderID
, SUM(a.quantity * b.price) AS totalSum
FROM @OrderDetails AS a INNER JOIN
@Products AS b ON a.productId = b.productId
GROUP BY GROUPING SETS ((orderID), ())
)
SELECT TOP(1) *
FROM rs
WHERE OrderID IS NOT NULL
ORDER by totalSum DESC;```

Output:
```orderID	totalSum
2	1170.00```
Tuesday, May 21, 2019 10:30 PM
• Thank you all,

Mona Lv, DIEGOCTN

There is some kind of a confusion here which is my fault. I really apology, i made a mistake in the code i wrote in my question.

Every order in orderDetails has several rows.

example:

orderId   orderDetailsId    quantiry   productid

1                1                     5              10

1                2                      7             3

1                3                      2              12

2                1                      5              8

2                2                      7              11

and so on. In order to get the order total per order we must sum (os.quantity*p.price) after grouping by orderId.

only once we have the sum for every order (the total per order) we can look for the max.  Now, once we have the max total (not the max row of quantity*price ) we need to add the order id to it.

The code i meant to write is:

select max(T.orderTot) from

(

select orderId, SUM(od.quantity*p.price) as orderTot

from OrderDetails OD, Products P

where p.prodId=OD.prodId

group by orderId

) T

The question is, how do i add the order id to the max total?

Soumen - having (od.quantity*p.price) = max(od.quantity*p.price) - this is not legal code.

ok thanks for the explanation. Here you go:

select orderId, sum(od.quantity*p.price) orderTot
from OrderDetails OD, Products P
where p.prodId=OD.prodId
group by orderId
having sum(od.quantity*p.price) = (select max(orderTot) from (select sum(od.quantity*p.price) orderTot
from OrderDetails OD, Products P
where p.prodId=OD.prodId
group by orderId) T

)

Plz mark as answer if it solves the problem. Thanks.

• Edited by Wednesday, May 22, 2019 4:18 AM
• Marked as answer by Wednesday, May 22, 2019 8:35 AM
Wednesday, May 22, 2019 4:12 AM
• Thank you all again.

i guess i could also do:

select top 1

od.orderId, SUM(od.quantity*p.price)

from OrderDetails od, Product p

where od.prodId=p.prodId

group by od.orderId

order by SUM(od.quantity*p.price) desc;

The unnecessary use of MAX() is what hold me.

Thank you my friends.

Wednesday, May 22, 2019 8:40 AM