I am having difficulty with writing an expression for counting the number of duplicate column pairs. For example, I have a table with two columns:

I want to count the number of matching pairs for buyer and seller, and create a new column (count) based on the total number of matching pairs, as such:

I know that the COUNT function is required to solve this, but am not sure how to implement it.

I would appreciate any help! Thanks.

CodePudding user response：

You want to GROUP BY the buyer and seller columns and then aggregate using the COUNT function:

SELECT buyer, seller, COUNT(*)
FROM   table_name
GROUP BY buyer, seller