SQL/Microsoft Access

Question

I am trying to figure out this problem but, it's already taking me a few days and I cant seem to solve it.

The questions demands:

Display a list of products (show ProductID and ProductDescription as the first two columns), the number of times each product has been ordered (the third column), and the total quantity each product has been ordered over all orders (the forth column).

The Database:

**Product_T**                                      **OrderLine_T**
ProductID  ProductDescription                 ProductID    OrderedQuantity
1          End Table                             1          2
2          Coffe Table                           2          2
3          Computer Desk                         4          1
4          Entertainment Center                  3          5
5          Writers Desk                          3          3
6          8-Drawer Desk                         6          2
7          Dining Table                          8          2
8          Computer Desk                         4          4
                                                 4          1
                                                 5          2
                                                 7          2
                                                 1          3
                                                 2          2
                                                 3          3
                                                 8          3
                                                 4          2
                                                 7          3
                                                 8          10

Answer 1

Just a JOIN and GROUP BY

SELECT p.ProductID,
       p.ProductDescription,
       COUNT(*) AS time_ordered,
       SUM(o.OrderedQuantity) AS qty_ordered
FROM Product_T as p
LEFT JOIN OrderLine_T AS o
    ON p.ProductID = o.ProductID
GROUP BY p.ProductID,
         p.ProductDescription;

Answer 2

Try this:

SELECT t1.ProductID,
       t1.ProductDescription,
       COALESCE(t2.num_times_ordered, 0) AS num_times_ordered,
       COALESCE(t2.total_quantity, 0)    AS total_quantity
FROM Product_T t1
LEFT JOIN
(
    SELECT ProductID,
           COUNT(*) AS num_times_ordered,
           SUM(OrderedQuantity) AS total_quantity
    FROM OrderLine-T
    GROUP BY ProductID
) t2
    ON t1.ProductID = t2.ProductID

The answer given by @GurV is more concise than this and works for this particular problem, but in general you would need to use a subquery to obtain stats from the OrderLine-T table, assuming that you wanted to include non-aggregate columns from Product_T in your report.