none
Add additional columns from a temp table RRS feed

  • 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

Answers

  • 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;
    
    -- final answer
    ;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 Soumen Barua Wednesday, May 22, 2019 4:18 AM
    • Marked as answer by Octopus01 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
    Please mark as answer if this post helped you
    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 Soumen Barua 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;
    
    -- final answer
    ;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 Soumen Barua Wednesday, May 22, 2019 4:18 AM
    • Marked as answer by Octopus01 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