How can I count unique pairs of values in SQL?

Question

With the following sql statement I can get all unique values with their counts for a given column:

select column, count(column) as count 
         from table group by column order by count desc;

How would I get all unique pairs of values with counts. For instance if I had a table of with columns first_name and last_name, I might find results something like this:

first_name | last_name | count

John | Smith | 42

John | Johnson | 39

David | Smith | 37

etc...

Can I do this in basic SQL? I generally use MySQL, but I assume whatever solution you come up with should be translatable to any db.

Answer 1

You've almost got it correct... You just add an additional GROUP BY column like so:

SELECT [first_name], [last_name], COUNT(*) AS [Count] 
FROM [Table]
GROUP BY [first_name], [last_name]
ORDER BY [Count] DESC;

Answer 2

If you just want the count of how many distinct pairs, you could do this more simply. A GROUP BY clause is not necessary.

SELECT COUNT(DISTINCT first_name, last_name) AS count_names FROM Table

Answer 3

Use multiple columns in your group by clause.

select first_name, last_name, count(*) as count from table group by first_name, last_name

Answer 4

SELECT first_name, last_name, COUNT(distinct last_name) AS c
FROM ttable
GROUP BY first_name, last_name
HAVING c > 999
ORDER BY c DESC 

Adding distinct will do it in MYSQL. Thanks