2

I have a MySQL table with the below schema:

Table - charges

+=============+==============+
|  chargeName |  clientId    |
+=============+==============+
|  late fee   |    123456    |
+-------------+--------------+
|  late fee   |    123456    |
+-------------+--------------+
|   payment   |    123456    |
+-------------+--------------+
|   refund    |    782151    |
+-------------+--------------+
|  late fee   |    782151    |
+-------------+--------------+

I need to return instances where the chargeName "late fee" appears more than once for the same clientId.

For example, given the above, I would expect my query to return "123456" because that clientId has 2 "late fees" associated with it.

I tried adapting this answer from SQL Query To Obtain Value that Occurs more than once 

SELECT clientId  
FROM 
    (SELECT chargeName, count(*) as Counter, clientId  
     FROM charges
     GROUP BY `chargeName`) 
AS tbl 
WHERE Counter > 1
AND chargeName='late fee'
limit 1000;

However, this query returns only one row (from my much larger dataset) and the clientId that is returned only has 1 late fee associated with it so clearly it is not working.

How can I return instances where the chargeName "late fee" appears more than once for the same clientId.

Community
  • 1
  • 1
Wesley Smith
  • 19,401
  • 22
  • 85
  • 133

1 Answers1

3

You can do it by GROUP BY and HAVING. 

SELECT clientID, chargeName
FROM charges
WHERE chargeName LIKE 'late fee'
GROUP BY clientID HAVING count(clientID) > 1
Kevin Kendzia
  • 248
  • 1
  • 12