Count the number of duplicate values on a column for specific values from an...
source link: https://www.codesd.com/item/count-the-number-of-duplicate-values-on-a-column-for-specific-values-from-another-column-in-mysql.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Count the number of duplicate values on a column for specific values from another column in MySQL?
I've spent ages researching this and found nothing that directly addresses the question.
I’m trying to see which customers are ‘like’ other customers at an aggregate level.
How do I count the number of duplicate values on one column for certain values of another column?
In the example below, I’d like to know how many records for ‘Customer 112’ and ‘Customer 113’ have duplicate variable values to those shown for “Customer 111”.
The answer is: Customer 112 = 3 (27, 28 and 30 are all duplicates of values shown for Customer 111)
and Customer 113 = 2 (24 and 26 are both duplicates of values shown for Customer 111)
.
Customer Variable
111 21
111 22
111 23
111 24
111 26
111 27
111 28
111 29
111 30
112 23
112 27
112 28
112 30
112 31
112 33
112 35
113 24
113 26
113 33
113 35
The output would be:
Customer Count
112 3
113 2
Any suggestions would be greatly appreciated.
Here is one way to do it, by joining to the "111" customer values and then aggregating:
select t.customer, count(t111.variable) as "count"
from t left outer join
(select t.*
from t
where customer = 111
) t111
on t.variable = t111.variable
group by t.customer;
I think the above is clear on what it is doing. However, you can eliminate the subquery (which is nice in MySQL):
select t.customer, count(t111.variable) as "count"
from t left outer join
t t111
on t.variable = t111.variable and t111.customer = 111
group by t.customer;
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK