Esta es la consulta:
Código PHP:
SELECT max(if(v.cSubSite = 'site0', v.dLastAccess,null)) as _UREALNAME, DATE_FORMAT(FROM_UNIXTIME(MAX((v.dLastAccess)),'%d/%m/%Y %h:%i:%s') AS LastAccess, DATE_FORMAT(FROM_UNIXTIME(v.dTimestamp),'%M %e, %Y %l:%i:%s') AS visit, u.nID, u.cFirstName as FirstName, u.cLastName as Lastname,
u.cEmail as Email, u.cDept as Location, COUNT(v.cURL) as TotalAccess, DATE_FORMAT(FROM_UNIXTIME(dCreated),'%M %e, %Y') as created, DATE_FORMAT(FROM_UNIXTIME(dUpdated),'%M %e, %Y') as updated, v.cURL as URL, u.nStatus, u.isAdmin as Admin, v.cSubSite as Site,
SUM(IF(v.cSubSite = 'site0', 1,0)) AS site0, SUM(IF(v.cSubSite = 'site1', 1,0)) AS site1, SUM(IF(v.cSubSite = 'site2', 1,0)) AS site2, SUM(IF(v.cSubSite = 'site3', 1,0)) AS site3
FROM users u LEFT JOIN visits v ON u.nID = v.nUserID
GROUP BY u.nID