MySQL choice within selection

Question

I'm not even sure if the title is correct, sorry if not!

I have a MySQL database that stores information about Wi-Fi devices collected from several access points.

Below is my instruction SELECT, which returns all devices found within 1 hour:

SELECT mac 
  FROM TBLWiFi 
GROUP BY 
       mac 
HAVING COUNT( mac ) >1 
       AND TIMESTAMP >= DATE_SUB( NOW( ) , INTERVAL 1 HOUR )
)

The results look like this:

╔═══════════════════╦══════════════════╦═════════════╗
║ mac               ║ timestamp        ║ reportedby  ║
╠═══════════════════╬══════════════════╬═════════════╣
║ B0:02:94:1D:3B:43 ║ 21/03/2014 12:27 ║ AP-3c9b33d0 ║
║ C0:3E:0F:0F:2D:EB ║ 21/03/2014 12:37 ║ AP-3c9b33d0 ║
║ 64:70:02:29:16:AE ║ 21/03/2014 12:32 ║ AP-3c9b33d0 ║
║ B0:02:94:1D:3B:43 ║ 21/03/2014 12:46 ║ AP-5c9b33d0 ║
║ B4:F0:AB:C2:F6:92 ║ 21/03/2014 12:46 ║ AP-3c9b33d0 ║
║ 00:26:AB:F1:7C:EE ║ 21/03/2014 12:45 ║ AP-5c9b33d0 ║
║ B0:02:94:1D:3B:43 ║ 21/03/2014 12:45 ║ AP-4c9b33d0 ║
║ 1C:C1:DE:6A:90:47 ║ 21/03/2014 12:56 ║ AP-4c9b33d0 ║
║ B4:F0:AB:C2:F6:92 ║ 21/03/2014 13:02 ║ AP-3c9b33d0 ║
║ 00:26:AB:F1:7C:EE ║ 21/03/2014 13:07 ║ AP-4c9b33d0 ║
╚═══════════════════╩══════════════════╩═════════════╝

Now I want to know which devices ( mac) have been registered with more than one access point ( reportedby).

I am in a flat rotation, my mind has completely disappeared. Any help would be appreciated.

Answer 1

First, your request should look like this:

SELECT mac 
FROM TBLWiFi 
WHERE TIMESTAMP >= DATE_SUB( NOW( ) , INTERVAL 1 HOUR )
GROUP BY  mac 
HAVING COUNT( mac ) > 1 ;

having timestamp timestamp mac. .

, having :

HAVING COUNT(mac) > 1 and
       COUNT(DISTINCT reportedby) > 1;

, reportedby, , :

HAVING COUNT(DISTINCT reportedby) > 1;

Answer 2

SELECT mac 
  FROM TBLWiFi 
GROUP BY 
       mac 
HAVING COUNT( reportedby ) >1 
       AND TIMESTAMP >= DATE_SUB( NOW( ) , INTERVAL 1 HOUR ))

, COUNT (mac) > 1... ... SELECT SELECT JOINT , 2 ... , ...: -)