 <pre><code>select TPNB, sum(AllocatedQty) as 'QTY' from ( SELECT TPND, TPNB FROM ProductLookup GROUP BY TPND, TPNB ) as PL inner join dbo.AllocatedStock as AStock on PL.TPND = AStock.TPND group by TPNB </code></pre>