Question: I have an "updates" table that can contain duplicate descriptions, and I would like to return the duplicates along with their count, so I created this query:

```
SELECT description, count(description) AS count
FROM updates INNER JOIN participations ON participations.status_id = updates.id
INNER JOIN customer ON customer.id = participations.customer_id
INNER JOIN garages ON garages.id = customer.garage_id
WHERE (updates.created_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH))
GROUP BY description
ORDER BY count desc

```

The counts returned were not what I was expecting. I believe the reason why is because many customers can share an update, so I am getting duplicates because of the actual duplicates in the table, and because the same update record is being returned multiple times. How can I fix the query so that it only counts the actual duplicate description fields in the update table. Thanks

Answer: 
I think you could just remove the joins with participations and garages, since you are not using them neither as filters nor are you getting thier fields on the select. But if you do use them, then it should do something like this:

```
SELECT description, count(description) AS count 
FROM updates
WHERE (updates.created_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) 
  AND EXISTS (SELECT 1 FROM participations INNER JOIN customer ON customer.id = participations.customer_id INNER JOIN garages ON garages.id = customer.garage_id WHERE participations.status_id = updates.id)
GROUP BY description 
ORDER BY count desc

```
According to [mysql manual](http://dev.mysql.com/doc/refman/5.0/en/select.html) you may use `HAVING` clause with fields generated with `GROUP BY`:

> 
> A HAVING clause can refer to any column or alias named in a select\_expr in the SELECT list or in outer subqueries, and to aggregate functions. However, the SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions.
> 
> 
> 

And example:

```sql
SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2; 

```

so you may use your query like:

```sql
SELECT description, count(description) AS count
FROM updates INNER JOIN participations ON participations.status_id = updates.id
INNER JOIN customer ON customer.id = participations.customer_id
INNER JOIN garages ON garages.id = customer.garage_id
WHERE (updates.created_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH))
GROUP BY description
HAVING count > 1
ORDER BY count desc

```