如何计算SQL中唯一的值对？ [英] How can I count unique pairs of values in SQL?

问题描述

使用以下sql语句，我可以获得所有唯一值及其对给定列的计数：

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;

如何获得所有唯一值对的计数。例如，如果我有一个与列first_name和last_name的表，我可能会找到类似这样的结果：

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 |计数

first_name | last_name | count

John |史密斯| 42

John | Smith | 42

John | Johnson | 39

John | Johnson | 39

David |史密斯| 37

David | Smith | 37

等...

我可以在基本SQL ？我一般使用MySQL，但我认为任何解决方案你应该可以翻译到任何数据库。

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.

推荐答案

正确...您只需添加一个额外的 GROUP BY 列：

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;

这篇关于如何计算SQL中唯一的值对？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！