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

Mysql – Using GROUP BY to find the total QTY of an item on its first ever order?

I’m trying to come up with a query that will pull the total quantity of an item from its first ever order. I understand how to pull out each item and their first order number was, but I’m trying to see each items first order, and the total amount of that item on that specific order. The item can have multiple lines on the same order which seems to be throwing me off. Here is an example table

Item Order# Quantity
A 1 10
A 1 15
A 2 5
A 2 5
B 3 50

I want the results to be like this where it just takes the items first order ( min() ) and sums the QTYs of the lines for that item on that order only:

Item Order# Quantity
A 1 25
B 3 50

I did something simple like this:

SELECT 
item, 
min(order), 
sum(quantity)

FROM orders 

GROUP BY item

It will show me the min order of course, but how do I get the sum to only sum the quantity of that specific item on that specific first order? Currently when I run it shows the first order with the item, but it sums the qty across all orders for the item and I’m having trouble. Any help would be appreciated. Thanks!

Tags:

2

Answers


  1. 0

    Join the table with a subquery that gets the first order number for each item.

    SELECT t1.item, t2.Order_num, SUM(t1.quantity) AS quantity
FROM orders AS t1
JOIN (
    SELECT item, MIN(Order_num) as Order_num
    FROM orders
    GROUP BY item
) AS t2 ON t1.item = t2.item AND t1.Order_num = t2.Order_num
GROUP BY t1.item, t2.Order_num

    DEMO

    order is a reserved word, so it’s best to avoid using it as a column name.

    Login or Signup to reply.
  2. 0

    One option is to use correlated subquery in the WHERE clause …

    --      S Q L :  
Select    o.Item, o.Order_num, Sum(o.Quantity) as Quantity
From      orders o
Where     o.Order_num = ( Select Min(Order_num) From orders Where Item = o.Item )
Group By  o.Item, o.Order_num
Order By  o.item

    … OR in the HAVING Clause …

    Select    o.Item, o.Order_num, Sum(o.Quantity) as Quantity
From      orders o
Group By  o.Item, o.Order_num
Having    Min(o.Order_Num) = ( Select Min(Order_num) From orders Where Item = o.Item )
Order By  o.item

    R e s u l t : ( same for both )

    Item Order_num Quantity
    A 1 25
    B 3 50

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search