T-SQL: Include a row count as a column for unique rows

Question

I have a table, we'll call TableA and contains the following 3 columns with data

Col1   Col2   Col3
a      b      c
a      b      c
d      e      f
g      h      i
g      h      i
g      h      i

I want to return a record set that looks like this:

Col1   Col2   Col3   Total
a      b      c      2
d      e      f      1
g      h      i      3

Duplicate rows are only returned once along with the count of their occurrences. Not sure how to formulate the sql. Thanks for your help!

Answer 1

Try this:

SELECT Col1, Col2, Col3, COUNT(*) AS Total
FROM TableA
GROUP BY Col1, Col2, Col3