CODEWITHSUNDEEP
mysql
Lukas J.
Lukas J.

Reputation: 196

MySQL count values from two tables

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

Upvotes: 1

Views: 161

Answers (2)

Glavić
Glavić

Reputation: 43582

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

Upvotes: 2

mathielo
mathielo

Reputation: 6795

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.

Upvotes: 0

Related Questions