CODEWITHSUNDEEP
mysqlsqlduplicates
Maximilian Krause
Maximilian Krause

Reputation: 1155

Find distinct, duplicate values in SQL and show them all

I've got a database of several datasets with latitude and longitude coordinates as well as corresponding data on the weather. I've tried this query:

SELECT distinct `lat`,`lon` 
FROM weatherdata 
WHERE `maxtemp`=0 AND `mintemp`=0 AND `humidity`=0 AND `windspeed`=0
HAVING COUNT(*)>300

in order to find coordinates where all the weather data is set to "0" in over 300 datasets. However, this only displays one coordinate set and I know for a fact that there are way more. What is the correct way of doing so?

Upvotes: 0

Views: 39

Answers (1)

David Manheim
David Manheim

Reputation: 2626

Try the following;

SELECT distinct `lat`,`lon` 
FROM (Select count(*) as c, `lat`,`lon` from weatherdata 
   WHERE `maxtemp`=0 AND `mintemp`=0 AND `humidity`=0 AND `windspeed`=0 
   GROUP BY `lat`, `lon` having c > 300)

Upvotes: 1

Related Questions