CODEWITHSUNDEEP
mysqldateunique
user2386810
user2386810

Reputation: 3

Select distinct ID's

We would like an SQL statement that lists the number of times a unique IP/uniqueID pair has visited on any unique date ordered by the maximum numbers of times that the UniqueID/IP pair has visited.

Here is the table structure:

Column             Type
------------------------------
Date               Timestamp
NumberofUsers      smallint
ipaddress          varchar(16)
location           varchar(2)
Count              bigint(20)

Here is the sql we have been trying:

SELECT
  LicenseID, 
  MAX(Date) AS LatestAccess,
  COUNT(DISTINCT Location) AS DifferentCountries,
  COUNT(DISTINCT IPAddress) AS DistinctIPCount,
  COUNT(DISTINCT Date,IPAddress) AS DistinctDate
FROM
  LicenseHistory 
WHERE
  (LicenseID<>30002)
GROUP BY
  LicenseID 
ORDER BY
  DistinctDate DESC

Here is some sample date from the table in CSV format:

2009-10-08 10:37,30002,8,24.108.64.80,CA,2399
2009-05-27 16:57,24508,50,24.108.64.80,CA,645
2008-11-06 12:04,30,100,24.108.64.80,CA,282
2008-02-04 10:51,24508,30,24.69.19.207,CA,62
2009-10-08 14:52,13136,5,24.108.64.80,CA,285
2013-05-13 13:10,718,10,66.251.68.106,US,23860
2008-02-12 11:10,30002,8,24.69.19.207,CA,36
2008-04-09 17:49,18504,10,70.90.32.57,US,121
2007-07-26 13:38,30002,8,76.226.201.191,US,2
2009-12-03 22:35,30002,8,196.25.255.214,ZA,14
2013-05-13 6:49,20341,4,66.232.201.125,US,2676
2007-07-28 23:57,30002,8,75.81.107.238,US,1
2007-07-29 10:39,30002,8,70.63.54.162,US,1
2007-07-30 3:53,30002,8,121.210.199.31,AU,4
2007-07-30 5:11,30002,8,41.207.67.10,KE,2

Here is some sample results (not correct yet, last column should not match second to last):

uniqueID  LatestAccess         DifferentCountries  DistinctIPCount  DistinctDate
--------------------------------------------------------------------------------
20677     2013-05-13 18:20:15  4                   162              162
27749     2013-05-14 05:30:59  7                   155              155
459       2013-05-13 11:12:47  2                   143              143
24965     2013-05-14 13:44:56  6                   123              123
25226     2013-05-06 16:11:56  3                   104              104
20370     2013-05-14 05:54:04  4                   100              100

The problem I think is in the "COUNT(DISTINCT Date,IPAddress) AS DistinctDate" piece.

Upvotes: 0

Views: 783

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Your date format has a time in it. So, I think all the dates are unique. Try this:

SELECT
  LicenseID, 
  MAX(Date) AS LatestAccess,
  COUNT(DISTINCT Location) AS DifferentCountries,
  COUNT(DISTINCT IPAddress) AS DistinctIPCount,
  COUNT(DISTINCT date(Date), IPAddress) AS DistinctDate
FROM
  LicenseHistory 
WHERE
  (LicenseID<>30002)
GROUP BY
  LicenseID 
ORDER BY
  DistinctDate DESC

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26343

You need a COUNT DISTINCT. Here's a guess because there's no table structure provided:

SELECT
  VisitDate,
  COUNT(DISTINCT IPAddress, UniqueID) AS UniqueVisits
FROM MyTable
GROUP BY VisitDate
ORDER BY UniqueVisits DESC

Or if your visit date is a datetime or timestamp, cut out the time part with the DATE function (note the changes on the second and fifth lines):

SELECT
  DATE(VisitDate),
  COUNT(DISTINCT IPAddress, UniqueID) AS UniqueVisits
FROM MyTable
GROUP BY DATE(VisitDate)
ORDER BY UniqueVisits DESC

Upvotes: 1

Related Questions