Hi Bhavbhuti!

I won't answer all your questions, just make a few suggestions.

First, aliasing all tables and using that alias for all columns are generally preferable whenever you have more than one table within a select, so I'll add that.

Second, I think you're looking for either out of two options that you shouldn't mix together:

a) a result set for all items, split into separate representatives where applicable.
b) a result set for all representatives, with their sales summed

Generally, your queries indicate that you're looking for the first of these.

Assuming you're using a recent version of Firebird, I'd say you could (logically speaking) simplify your query by using a CTE. This is the query I'd try:

WITH SumbQty(iRepresentativeID, iItemID, bQtySum) as
(SELECT si.iRepresentativeID, sii.iItemID, SUM(sii.bQty)
FROM tSaleInvoice si
JOIN sSaleInvoiceItem sii
ON si.iID = sii.iPID
WHERE CAST(si.tDt AS DATE) BETWEEN '1.1.2010' AND '31.1.2010'
GROUP BY 1, 2)

SELECT si.iRepresentativeID, UPPER(i.cDesc) as cDesc, UPPER(i.cPack) as cPack, sq.bQtySum as bQty
FROM mItems i
LEFT JOIN sSaleInvoiceItem sii
ON i.iID = sii.iItemID
LEFT JOIN tSaleInvoice si
ON sii.iPID = si.iID
LEFT JOIN SumbQty sq
ON si.iRepresentativeID = sq.iRepresentativeID
AND sii.iItemID = sq.iItemID
order by 1, 2, 3

This probably doesn't solve your entire problem, but hopefully give you new thoughts that will help you find a solution.

HTH,
Set