CODEWITHSUNDEEP
mysqlselectuniquedistinct
user2386810
user2386810

Reputation: 3

Find max distinct in Mysql

I have a database that records the uniqueID, location (country code), IPaddress and date of visitors. I need to craft an sql statement that builds a list (and counts) of the maximum number of uniqueID visits for each IPaddress on each unique date. Can any of you supply some samples, hints?

Thanks, Gary

Upvotes: 1

Views: 120

Answers (3)

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

Select uniqueId , ipAddress , uniqueDate , count (*) as visitsTotal From utable Group by uniqueId,ipAddress , uniqueDate

The Group By clause ensures uniqueness

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you just want a simple aggregation:

select IpAddress, date, count(*)
from t
group by IpAddress, date;

If you want to count distinct visitors and put them in a list, you need a visitor id. Perhaps that is what uniqueId is. If so:

select IpAddress, date, count(distinct UniqueId),
       group_concat(distinct UniqueId)
from t
group by IpAddress, date;

Upvotes: 1

Lefteris E
Lefteris E

Reputation: 2814

Try this:

select ip, dateVisited, count(*) group by ip, dateVisited

Upvotes: 0

Related Questions