0

I have two tables called PRODUCT and LINEITEMS and I am trying to use a combination of a JOIN and GROUP BY to display a single row for each product with the total quantity sold next to it. The structure of the tables is as follows:

  • PRODUCT (partno#, description, category, unitcost, unitprice)
  • LINEITEM (order#, line#, partno#, quantity, unitprice

I've got this far but the quantity displayed next to each product is the sum of all quantities from all products and not the sum of the quantity sold for that product on the line.

SELECT p.description, sum(li.quantity)
FROM product p, linetem li
GROUP BY p.description;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Adam T
  • 3
  • 1
  • 2
    Yes, the one you are using is a cross join. If you don't want that it is better if you use inner join or other join explicitly on any columns from these two tables – Avi May 19 '19 at 12:16

1 Answers1

1

Never use commas in the FROM clause.

Always use proper, explicit, standard JOIN syntax.

Your query is missing JOIN conditions. Something like this:

SELECT p.description, sum(li.quantity)
FROM product p JOIN
     linetem li
     ON p.partno# = li.partno#
GROUP BY p.description;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786