Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL, counting pairs of values in a table

Tags:

sql

tsql

Given a table in the format of

ID   Forename    Surname
1    John        Doe
2    Jane        Doe
3    Bob         Smith
4    John        Doe

How would you go about getting the output

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

For a single column I would just use count, but am unsure how to apply that for multiple ones.

like image 456
John Avatar asked Oct 14 '10 15:10

John


2 Answers

SELECT Forename, Surname, COUNT(*) FROM YourTable GROUP BY Forename, Surname
like image 83
Larry Lustig Avatar answered Sep 18 '22 02:09

Larry Lustig


I think this should work:

SELECT Forename, Surname, COUNT(1) AS Num 
FROM T
GROUP BY Forename, Surname
like image 29
Michael Goldshteyn Avatar answered Sep 18 '22 02:09

Michael Goldshteyn