CODEWITHSUNDEEP
mysql
Robbie
Robbie

Reputation: 716

Count quantity of results for duplicate rows sql

I have a table similar to this:

 customer         employee
------        ---------
  A              Adam
  A              Adam
  A              Adam
  B              Bob
  C              Adam
  C              Adam
  B              Bob
  D              Dan

I'm trying to figure out how to show how many times Adam has had repeat customers and how many times Bob has. So in the example above it would return:

Adam => 2 (he's had 2 repeat customers, customer A and C)

Bob => 1 (he's had 1 repeat customer, customer b)

Dan => 0 (he's only had one customer, no repeats)

What I have so far is:

SELECT customer, COUNT(*) as cnt
FROM table
GROUP BY employee
HAVING cnt > 1

But I just can't seem to get the rest of the way.

Upvotes: 3

Views: 442

Answers (5)

Rahul Babu
Rahul Babu

Reputation: 790

select employee, count(*)-1 as cnt
from t1
group by employee,customer
having cnt > 0;

Upvotes: 0

matinict
matinict

Reputation: 2490

IF you COUNT repeat employee :

SELECT id,customer,employee,COUNT(*) FROM USER  GROUP BY employee HAVING COUNT(*)>0

enter image description here

IF you COUNT repeat customer :

SELECT id,customer,employee,COUNT(*) FROM USER  GROUP BY customer HAVING COUNT(*)>0

enter image description here

//Note: I take total count, you change )COUNT()>0 to COUNT()>1 && Table=user as

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer` varchar(200) DEFAULT NULL,
  `employee` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

/*Data for the table user */

insert  into `user`(`id`,`customer`,`employee`) 
values (1,'A','Adam'),(2,'A','Adam'),(3,'A','Adam'),(4,'B','Bob'),(5,'C','Adam'),(6,'C','Adam'),(7,'B','Bob'),(8,'D','Dan');

Upvotes: 0

vhu
vhu

Reputation: 12818

You first need to find the repeat customer:

SELECT employee,customer FROM yourtable GROUP BY employee, customer having COUNT(*) > 1;

After that you can find how many repeat customers each employee has had:

SELECT employee, COUNT(*) from (
 SELECT employee,customer 
  FROM yourtable 
  GROUP BY employee, customer 
  HAVING COUNT(*) > 1
 ) t
GROUP BY employee;

Upvotes: 2

Satender K
Satender K

Reputation: 581

You can try the below query :

SELECT 
    t.employee, COUNT(*) AS cnt
FROM
    (SELECT 
        employee, customer, COUNT(*) AS actcnt
    FROM
        customers
    GROUP BY customer , employee HAVING actcnt > 1) AS t
GROUP BY t.employee
ORDER BY t.employee;

DEMO

Upvotes: 3

Rahman Qaiser
Rahman Qaiser

Reputation: 682

SELECT customer, employee FROM table GROUP BY customer, employee HAVING count(*) > 1

Upvotes: -1

Related Questions