Question 21828 in Mysql Question posted in
The official MySQL documentation can be found here.

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

The OM Database.

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.

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    Use MySQL to achieve your desired idea.

    SELECT
    tb.*
FROM
    (
    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
    ) AS tb
WHERE
    tb.sum_price = (
    SELECT
        MAX(tb.sum_price)
    FROM
        (
        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
        ) AS tb
    )
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search