Hi all

I tried the following query and I get a SUM of qty and NULL for each
item that did not have any sale
SELECT mItems.cDesc, mItems.cPack, (SELECT SUM(sSaleInvoiceItem.bQty)
FROM sSaleInvoiceItem WHERE sSaleInvoiceItem.iItemID = mItems.iID) AS bQty
FROM mItems
WHERE cTypeID = 'FIN'
AND (mItems.lHierarchialStock = 1
OR NOT EXISTS (SELECT 1
FROM mItems d
WHERE d.iPID = mItems.iID))
AND mItems.cTypeID IN ('FIN')
ORDER BY UPPER(mItems.cDesc), UPPER(mItems.cPack)

Now I want to generate a similar set of records but for each
representative, so I tried the following
SELECT mItems.cDesc, mItems.cPack
, (SELECT SUM(sSaleInvoiceItem.bQty) FROM sSaleInvoiceItem WHERE
sSaleInvoiceItem.iItemID = mItems.iID) AS bQty
FROM mItems
JOIN sSaleInvoiceItem
ON sSaleInvoiceItem.iItemID = mItems.iID
JOIN tSaleInvoice
ON tSaleInvoice.iID = sSaleInvoiceItem.iPID
JOIN mAccounts
ON mAccounts.iID = tSaleInvoice.iRepresentativeID
WHERE mItems.cTypeID = 'FIN'
AND (mItems.lHierarchialStock = 1
OR NOT EXISTS (SELECT 1
FROM mItems d
WHERE d.iPID = mItems.iID))
AND mItems.cTypeID IN ('FIN')
ORDER BY tSaleInvoice.iRepresentativeID, UPPER(mItems.cDesc),
UPPER(mItems.cPack)

and I loose the NUL bQty records, plus I can't
GROUP BY tSaleInvoice.iRepresentativeID, mItems.cDesc, mItems.cPack
else it gives an error
Invalid expression in the select list (not contained in either an
aggregate function or the GROUP BY clause)

Please advise.

Thanks and regards.
Bhavbhuti