gvanaco wrote:I would count how many rows that are exactly the same.
So you want a total count of how many duplicates there are? Or the number of duplicates for each row? If you want just a total number, something like this should work:
SELECT SUM(count) AS total FROM (
SELECT (COUNT(*)-1) AS count
FROM coords
GROUP BY x, y, `date`
) AS a
If you wanted a list of rows & how many duplicates they have, you could do something like this:
SELECT (COUNT(*)-1) AS count, x, y, `date`
FROM coords
GROUP BY x, y, `date`
HAVING count > 0
I tested this, and it seemed to work for me, though I'm no SQL guru so you might want to test it with your data to be sure.
gvanaco wrote:The second step is to do the same query but only on a specific (range) date.
Quite easy... just add a "WHERE date = 'date here'" onto the queries. For the first query, you would add it to the subquery.
