I have two identical tables - logins and visits. Which contains:
ID | address | mac_address | date
How can I make a php query to count logins and visits from mac address kind of this:
MAC | Logins | Visits
For now I have
SELECT mac_address, COUNT(mac_address) AS login_cnt FROM logins GROUP BY mac_address ORDER BY login_cnt DESC
which gives me just
MAC | Logins
SELECT
mac_address,
SUM(logins_mac_count) AS logins_mac_sum,
SUM(visits_mac_count) AS visits_mac_sum
FROM (
SELECT mac_address, COUNT(*) AS logins_mac_count, 0 AS visits_mac_count
FROM logins
GROUP BY mac_address
UNION
SELECT mac_address, 0 AS logins_mac_count, COUNT(*) AS visits_mac_count
FROM visits
GROUP BY mac_address
) AS t
GROUP BY mac_address
Try this:
SELECT
IFNULL(logins.mac_address, visits.mac_address) AS unique_mac_address,
COUNT(login.mac_address) AS count_logins,
COUNT(visits.mac_address) AS count_visits
FROM logins
LEFT JOIN visits ON logins.mac_address = visits.mac_address
GROUP BY unique_mac_address
Note: I haven't tested this as I don't have your schema/test data here, but I think this should work. Let me know if not.
visits which doesn't appear in logins.
SELECT ( SELECT UNION ) GROUP BY then?SELECT UNION won't help either, as it will sum up the vistis + logins counts. As far as I get it, he needs these values to be distinct.
FROM should be from table visits, and JOIN from logins.