When aggregating values in queries, I found a problem that records from the
"master" table are not shown in query results when aggregated values are
null, e.g. (from DBDemos database):

SELECT DISTINCT d2.PartNo, d2.Description, sum(d1.Qty * d2.ListPrice)
FROM "orders.db" d, "items.db" d1, "parts.db" d2
WHERE
 EXTRACT(YEAR FROM SaleDate)=1995
 AND (d1.OrderNo = d.OrderNo)
 AND (d2.PartNo = d1.PartNo)
GROUP BY d2.PartNo, d2.Description

does *not* show those parts that were not ordered in the year 1995.

Thanks in advance,

Bojan

SELECT DISTINCT d2.PartNo, d2.Description, sum(d1.Qty * d2.ListPrice)
FROM "parts.db" d2 LEFT OUTER JOIN "items.db" d1
ON (d2.PartNo = d1.PartNo)
JOIN "orders.db" d
ON (d1.OrderNo = d.OrderNo)
WHERE
 EXTRACT(YEAR FROM SaleDate)=1995
GROUP BY d2.PartNo, d2.Description

--
Bill
(TeamB cannot answer questions received via email)

Bill, thanks, but this query produced exactly the same results (38 record)
as my query
(SELECT DISTINCT d2.PartNo, d2.Description, sum(d1.Qty * d2.ListPrice)
FROM "orders.db" d, "items.db" d1, "parts.db" d2
WHERE
EXTRACT(YEAR FROM SaleDate)=1995
AND (d1.OrderNo = d.OrderNo)
AND (d2.PartNo = d1.PartNo)
GROUP BY d2.PartNo, d2.Description)

i.e. parts with no sales are still *not* shown in the answer table.
Any idea?

TIA, Bojan

Sorry. I forgot to change the WHERE clause to allow null SaleDates.

SELECT DISTINCT d2.PartNo, d2.Description, sum(d1.Qty * d2.ListPrice)
FROM "parts.db" d2 LEFT OUTER JOIN "items.db" d1
ON (d2.PartNo = d1.PartNo)
JOIN "orders.db" d
ON (d1.OrderNo = d.OrderNo)
WHERE
 EXTRACT(YEAR FROM SaleDate)=1995 or SaleDate IS NULL
GROUP BY d2.PartNo, d2.Description

--
Bill
(TeamB cannot answer questions received via email)

Bill, sorry for bothering, but this seems does not solve the problem: parts
with null sales are still not shown.

TIA, Bojan

Try it this way.

SELECT DISTINCT d2.PartNo, d2.Description, sum(d1.Qty * d2.ListPrice)
FROM "parts.db" d2 LEFT OUTER JOIN "items.db" d1
ON (d2.PartNo = d1.PartNo)
LEFT OUTER JOIN "orders.db" d
ON (d1.OrderNo = d.OrderNo)
WHERE
 EXTRACT(YEAR FROM SaleDate)=1995 or SaleDate IS NULL
GROUP BY d2.PartNo, d2.Description

--
Bill
(TeamB cannot answer questions received via email)

Sorry, but no difference.
The problem is obviously in WHERE clause: if I omit it, the query works just
fine (you must actualy add one new part record to check this, bacause all
parts were ordered at least once in the whole period).
I also try to replace LEFT (OUTER JOIN) with FULL - no success.

TIA, Bojan

After some additional testing I found out there's no benefit of using OUTER
JOIN (both queries produces exactly the same result as soon as I include
WHERE clause). I found a workaround by UNION of those parts sold and *not*
sold in 1995:

SELECT P.PartNo, P.Description, SUM(I.Qty * P.ListPrice) AS Sales_1995
FROM "parts.db" P, "items.db" I, "orders.db" O
WHERE (P.PartNo=I.PartNo) AND (I.OrderNo=O.OrderNo)
 AND (EXTRACT(YEAR FROM SaleDate)=1995)
GROUP BY P.PartNo, P.Description

UNION
SELECT P.PartNo, P.Description, CAST (0 AS MONEY)
FROM "parts.db" P
WHERE NOT (P.PartNo IN
  (SELECT DISTINCT I.PartNo FROM "items.db" I, "orders.db" O
 WHERE (I.OrderNo=O.OrderNo) AND (EXTRACT(YEAR FROM O.SaleDate)=1995)))

The problem is that even this simple example becomes complex (my real
queries are in fact much more complex). I'm also not sure about the
performance. Additionally, I can't ORDER BY the result set (unless I save
the query and order record in another query based on the first one).

Anyone has simpler, more powerful solution?

TIA, Bojan