How to Count Unique Pairs of Values in Sql

How can I count unique pairs of values in SQL?

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;

Count distinct value pairs in multiple columns in SQL

To get a count of the number of unique combinations of id, name and address:

SELECT Count(*)
FROM   (
        SELECT DISTINCT
               id
             , name
             , address
        FROM   your_table
       ) As distinctified

How to count number of distinct pairs per partition in SQL?

You can join both column into one Text, and count the concatenated text

COUNT(DISTINCT CONCAT(group_no, item_no)) 
  OVER (PARTITION BY level)
AS item_count

distinct pairs plus the number of times each pair appears

select 
    a,b,count(*) c
from 
    pairs
group by 
    a,b

Is there a way to count unique pairs and group by other columns

Concatenate the 2 values and then
COUNT the DISTINCT values:

SELECT COUNT(DISTINCT CONCAT(TrackTitle,'|',MainArtist)) AS [Count],
       Station,
       [Year]
FROM dbo.YourTable
GROUP BY Station,
         [Year];

How should we count the number of distinct pairs / tuples in SQL?

To return the count of each distinct firstname lastname, you would use COUNT and GROUP BY:

SELECT FirstName, LastName, COUNT(*)
FROM TableName
GROUP BY FirstName, LastName

In your above example, I think you meant Zhang to have a count of 2 though.

--EDIT

If I'm understanding your latest comment correctly, you want to also use DISTINCT:

SELECT FirstName, LastName, Age, Id, COUNT(*)
FROM (SELECT DISTINCT FirstName, LastName, Age, Id FROM TableName) T
GROUP BY FirstName, LastName, Age, Id

Good luck.

How can I count the number of unique pairs in a table that has a recursive relationship?

I think the simplest method is:

select count(*)
from people_table
where PersonId < CoupleId  ;

Why does this work? Well, two people in a couple have different ids. One of them must be smaller than the other and this counts one of them.

This also filters out NULL values.

Note: This assumes that your data is well-formed -- that is, both persons in a couple are in the table as separate rows.

---

Related Topics