WITH totals (code, qty) AS (SELECT code, SUM(qty) FROM #Table1 GROUP BY code) SELECT t1.code, tn.Names, tn.Country, t1.qty FROM totals t1 CROSS APPLY (SELECT TOP (1) names, country FROM #Table1 t2 WHERE t1.code = t2.code) tn(Names, Country) ORDER BY t1.code;