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.
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;