Sum of orders per customer ID

Question

So, I have two tables, customers and orders and I want to get all orders associated with a customer.

The tables look like this:

Customers

customer_no   order_no
       1027       V327
       1027       V391
       1028       V400
etc..

Orders

order_no article1 article2 size order_date quantity
    V327    Jeans    Black   36   20150101        2
    V327    Shirt     Blue    L   20150101        1
    V327    Shirt    White    L   20150101        2
    V391    Jeans      Red   34   20150202        3
    V400    Shirt    Green   32   20150226        2
etc..

Now, I currently use the following query, but it is dog slow and I just know I'm approaching this wrongly. I feel like it should be possible to solve this with a join somewhere, but for the life of mine, I just cannot figure this one out.

SELECT article1, article2, size, order_date, SUM(quantity)
FROM orders
WHERE order_no IN (
    SELECT order_no
    FROM customers
    WHERE customer_no = '1027'
)
GROUP BY article1, article2, size, order_date, SUM(quantity)

The output for customer 1027 is (and should be):

article1 article2 size order_date SUM(quantity)
   Jeans    Black   36   20150101             2
   Shirt     Blue    L   20150101             1
   Shirt    White    L   20150101             2
   Jeans      Red   34   20150202             3

Does anyone have a better way of solving this? Performance is my main issue here.

Answer 1

Applying a JOIN sounds pretty easy

SELECT article1, article2, size, order_date, SUM(quantity)
FROM orders 
INNER JOIN customers ON orders.order_no = customers.order_no
WHERE customers.customer_no = '1027'
GROUP BY article1, article2, size, order_date, SUM(quantity)

You should note that nested SELECT statements in a WHERE clause almost always lead to bad performance.

---

Also that kind of unnormalized data model looks weird, but there may be good reasons why to keep order_no and customer_no pairs in a single association table. May be its just bad naming, something like customers_orders might probably have been a better choice.

Answer 2

I would assume that an INNER JOIN would be better:

SELECT customers.article1
    , customers.article2
    , customers.size
    , customers.order_date
    , SUM(customers.quantity)
FROM orders
INNER JOIN customers
    ON customers.order_no = orders.order_no
WHERE customers.customer_no = '1027'
GROUP BY customers.article1
    , customers.article2
    , customers.size
    , customers.order_date
    , SUM(customers.quantity)

Also, the SUM(customers.quantity) in the GROUP BY looks really odd.

Answer 3

Using an INNER JOIN would solve your issue:

SELECT 
 orders.article1, 
 orders.article2,
 orders.size, 
 orders.order_date, 
 SUM(orders.quantity) AS [Quantity]
FROM 
 orders INNER JOIN customers ON orders.order_no = customers.order_no
WHERE 
 customer.cust_no = 1027
GROUP BY 
 orders.article1, 
 orders.article2, 
 orders.size, 
 orders.order_date

The difference is that I removed the SUM(orders.quantity) from the GROUP BY since it doesn't belong in that statement. I corrected @BCdotWEB since he used the customers table and not the orders table when calling columns. Also, I am not sure how the voted answer worked as there should have been an error due to the SUM(orders.quantity) being used in the Group By.