I am trying to craft a query that will select multiple columns (uniqueID, IPaddress, date, country) but I would like the query only to select those records where the same IPaddress and UniqueID showed up on a given day. I have been trying ...
SELECT uniqueID AS uniqueID, IPAddress AS IPAddr, Date AS DDate, Location AS Countries, COUNT(IPAddress) AS IPCount FROM History WHERE (uniqueID= 20677) GROUP BY Date ORDER BY DDate DESC, IPAddr DESC
But it does eliminate records where only one Ip/UniqueID showed up on one day. I really only want to find records where the <strong>uniqueID showed up on the same day with different IPaddresses</strong>.Answer1:
Not sure if I follow exactly, but sounds like you want to join a subquery:
SELECT a.* FROM History a JOIN ( SELECT uniqueID, Date, COUNT(DISTINCT IPAddress) FROM History GROUP BY uniqueID,Date HAVING COUNT(DISTINCT IPAddress) > 1 )b ON a.uniqueID = b.uniqueID AND a.date = b.date
EDIT: misread your with different IP addresses part, updated.
The subquery will show you all the records that have multiple ip addresses for the same uniqueID/date, then joining that to your history table shows you all the fields for the records you're after.Answer2:
You need to group by three fields, if you are looking for duplicates amongst them:
SELECT uniqueID, IPAddress, "Date" AS DDate, Location AS Countries, COUNT(IPAddress) AS IPCount FROM History WHERE uniqueID = 20677 GROUP BY uniqueId, IPAddress, "Date" having IPCount > 1;
You dn't really need the
uniqueid in the
group by clause because you are selecting only one. However, I think it makes the intention of the query clearer.
As written, this will either generate an error (because
location is not in the aggregation clause) or return a random value for
location, if you are using MySQL). If the latter, try:
SELECT uniqueID, IPAddress, "Date" AS DDate, group_concat(Location) AS Countries, COUNT(IPAddress) AS IPCount FROM History WHERE uniqueID = 20677 GROUP BY uniqueId, IPAddress, "Date" having IPCount > 1;
Also, naming columns after data types and functions is not good idea. In many databases,
date is both a data type and a function. Some other name, like
DateAdded avoids this conflict.