I need to show the order ID and the order total for largest order (order ID-dollar amount)?-CodePudding

I need to find the order ID and the largest order in terms of dollars using SQL. I attached the OM database. Using SQL I think I would need to use group by or order by but I just need to find the largest order.

select order_id, sum(unit_price) from items;

It resulted in an error. I wanted it to show the order id and the sum of the unit_price and get the largest unit price to get the largest order. I’m getting an error message saying query without GROUP BY and nonaggregated column.

CodePudding user response：

Your query would normally only return one row, but as you dind't add an aggegation function, the error message occours

select order_id, sum(unit_price) 
from items
GROUP BY order_id
ORDER BY sum(unit_price) DESC
LIMIT 1;

This would give you only 1 order back,

if there are multiple with same sum you need another approach

WITH CTE As 
(    select order_id, sum(unit_price) sum_p
    from items
    GROUP BY order_id
    )
SELECT order_id,sum_p FROM CTE WHERE sum_p = (SELECT MAX(sum_p) FROM CTE)

CodePudding user response：

Use MySQL to achieve your desired idea.

SELECT
    order_details.order_id,
    SUM(items.unit_price) AS 'sum_price'
FROM
    order_details,
    items
WHERE
    order_details.item_id = items.item_id
GROUP BY
    order_details.order_id
ORDER BY
    SUM(items.unit_price)
DESC
LIMIT 1