-3

I have 2 relational tables orders and order_items orders has

  • id

  • customer_name

  • delivery_date

order_items has

  • order_id
  • item
  • unit
  • qty

I need to see how much (ie. Sum(qty)) of each item/unit combination each customer ordered with in a specified date range.

the only way I see this can be done is to use C# or vb.net and first create a datatable with distinct item/unit combinations for the date range. The I would loop through those item/units and get a total for a customer for them in that date range and add them to another datatable.

Is there a way to do this in sql alone?

boruchsiper
  • 2,016
  • 9
  • 29
  • 53

1 Answers1

0

Yes, there is:

SELECT customer_name, item, unit, SUM(Qty) as totals
FROM orders o INNER JOIN order_items oi 
  ON o.id = oi.order_id
WHERE o.delivery_date BETWEEN @datefrom AND @dateto
GROUP BY  customer_name, item, unit
ORDER BY  o.delivery_date
Milen
  • 8,697
  • 7
  • 43
  • 57
  • For performance, wouldn't it be better to put the date condition inside the `ON` and not inside the `WHERE`? – P. Camilleri May 09 '14 at 08:48
  • 1
    I doth think that this will make any difference in performance using `INNER JOIN` – Milen May 09 '14 at 08:49
  • @M.Massias: The query optimizer should resolve this correctly in either case. – Kahn May 09 '14 at 08:53
  • Wasn't criticizing or suggesting an improvment, it's a genuine question for my personal knowledge :) because if the query execution planner does it right, then you could (theoretically) do a cross join, and put all of your conditions in a WHERE after that ? – P. Camilleri May 09 '14 at 08:57
  • 2
    @M.Massias have a look at this: http://stackoverflow.com/questions/10297231/where-clause-vs-on-when-using-join – Milen May 09 '14 at 08:59