CODEWITHSUNDEEP
mysqlsqlgroup-bycountdistinct
Barney Chambers
Barney Chambers

Reputation: 2783

MYSQL - Select count of all rows that are duplicates in 1 column and distinct in another column

I have a MYSQL table called transactions.addresses which contains a username and address column. When a user creates a transaction on my website, it is logged in my database as shows below

|username|    address   |
--------------------------
| jeremy | a33bfd019    |
| jeremy | b583ffs9a    |
| ben    | c09123jca    |

How do I run a query that will show a count of all address fields that are found more than once in the table and have more than 1 different username associated with them in the database? In other words, I want to see how many different users have used the same address value in their transactions. Using the below dataset as an example:

|username|    address   |
--------------------------
| jeremy | a33bfd019    |
| jeremy | b583ffs9a    |
| jeremy | b583ffs9a    |
| ben    | c09123jca    |
| ben    | b583ffs9a    |

I would expect to see a result as follows:

|address   | count  |
--------------------------
| b583ffs9a| 2      |
| c09123jca| 1      |
| a33bfd019| 1      |

the address b583ffs9a should have a count of 2 because 2 users have used this address.

Upvotes: 0

Views: 31

Answers (1)

GMB
GMB

Reputation: 222432

As commented by Barmar, you can just aggregate by address and count distinct usernames in each group:

select address, count(distinct username) no_usernames
from mytable
group by address

Upvotes: 2

Related Questions