TSQL, counting pairs of values in a table

Question

Given a table in the format

  ID Forename Surname
 1 John Doe
 2 jane doe
 3 Bob Smith
 4 John Doe

How are you going to get the output

  Forename Surname Count
 John doe 2
 Jane doe 1
 Bob Smith 1

For a single column, I would just use count, but not sure how to apply this to multiple.

+6

sql tsql

John Oct 14 '10 at 15:05

source share

2 answers

I think this should work:

 SELECT Forename, Surname, COUNT(1) AS Num FROM T GROUP BY Forename, Surname

+1

Michael Goldshteyn Oct 14 '10 at 15:07

source share

Larry lustig · Accepted Answer · 2010-10-14T15:06:37+0000

SELECT Forename, Surname, COUNT(*) FROM YourTable GROUP BY Forename, Surname

TSQL, counting pairs of values ​​in a table