Q&A for professionals and students of engineering
declare @table table (mark int, name varchar(10), cost int, salecategory varchar(10), costcategory varchar(10)) insert @table values(10, 'pete', 20, 'b', 'b') insert @table values(10, 'pete', 20, 'b', 'b') insert @table values(15, 'pete', 20, 'c', 'b') insert @table values(10, 'pete', 56, 'c','b') insert @table values(15, 'John', 10, 'd','e') insert @table values(20, 'John', 10, 'a','b') insert @table values(21, 'John', 10, 'd','d') SELECT distinct mark, name, count(1) as txn FROM @table group by mark,name