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

Mysql – SQL – Select the ID corresponding to the max of a sum of values

This is the question in my hw
Retrieve cartId having the maximum number of food items.
Sample Data: (carts have multiple instances because the unitsInCart could be different items ie. 3 apples and 4 bananas)

cart_items table

cartId unitsInCart
cart 1 3
cart 1 4
cart 1 5
cart 2 4
cart 2 10

I can sum and group by ID to give me

cartId foodItems
cart 1 12
cart 2 14

But i can’t figure out the query to then select the max from these 2.

This is what I have at the moment

SELECT cartId, MAX(unitsInCart)
FROM (SELECT cartId, SUM(unitsInCart) 
FROM cart_items,
GROUP BY cartId);

I have tried various iterations of this can’t even remember.

Tags:

2

Answers


  1. 0

    You can use sub query like his

    SELECT cartId
FROM (
    SELECT cartId, SUM(unitsInCart) AS foodItems
    FROM cart_items
    GROUP BY cartId
) AS summed_carts
ORDER BY foodItems DESC
LIMIT 1;
    Login or Signup to reply.
  2. 0

    Adjust your query slightly. The problem with the current one is that it tries to find the maximum number of units in each cart directly in the main query, without properly aggregating them by cartId. Instead, you should first calculate the total units for each cart in a subquery, and then select the cartId with the highest total in the main query.

    SELECT cartId, SUM(unitsInCart) AS totalFoodItems
FROM cart_items
GROUP BY cartId
ORDER BY totalFoodItems DESC
LIMIT 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search