CODEWITHSUNDEEP
mysqlsql
Mike Graham
Mike Graham

Reputation: 25

MYSQL Multiple column COUNT DISTINCT

I keep a track of whom is logging onto our local server as shown in the database below.

My DB Structure is: 

user     logon          reconnect

mike     2015-07-09
mike                    2015-07-09
mike                    2015-07-09
mike     2015-07-09
john     2015-07-09
john     2015-07-09
john     2015-07-09
pete                    2015-07-09
pete                    2015-07-09
pete                    2015-07-09
matt     2015-07-09
sara     2015-07-09

I am trying to build a query that gets how many DISTINCT users logon or reconnect during one day, Data should look like this:

date          totalcount
2015-07-09    4

Basically its counted 4 users on 2015-07-09.

Heres my query that shows the last 14 days logons, but does not include reconnects

SELECT DATE(logon) AS `date`, COUNT(DISTINCT `user`) AS totalcount
FROM user_logons
GROUP BY DATE(logon)
ORDER BY DATE(logon) DESC
LIMIT 14

Shows:

date         totalcount
2015-07-09   399
2015-07-08   513
2015-07-07   524
2015-07-06   456
2015-07-05   213
2015-07-04   300
2015-07-03   484
2015-07-02   525
2015-07-01   539
2015-06-30   536
2015-06-29   481
2015-06-28   289
2015-06-27   423
2015-06-26   509

I'm wanting to total both columns, combining them and then grouping by date. I'm having trouble writing the query. Please help.

Upvotes: 1

Views: 67

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

My approach was to create two temporary tables. The first one contains user and a date column for all logon events. The second one contains a user and a date column for all reconnect events. After UNIONing these two tables together, you can group by the date and then get the count of distinct users for each date.

SELECT t.date AS `date`, COUNT(DISTINCT t.user) AS totalcount
FROM
(
    SELECT `user`, DATE(logon) AS `date`
    FROM user_logons
    WHERE DATE(reconnect) ISNULL
    UNION ALL
    SELECT `user`, DATE(reconnect) AS `date`
    FROM user_logons
    WHERE DATE(logon) ISNULL
) t
GROUP BY t.date
ORDER BY t.date DESC

Let me add that this approach would be more attractive if you had values other than NULL, in which case you could not use COALESCE() so gracefully.

Upvotes: 1

PM 77-1
PM 77-1

Reputation: 13334

Use COALESCE() function to "combine" the two dates:

SELECT DATE(COALESCE(logon, reconnect)) AS `date`, COUNT(DISTINCT `user`) AS totalcount
FROM user_logons
GROUP BY DATE(COALESCE(logon, reconnect))
ORDER BY DATE(COALESCE(logon, reconnect)) DESC
LIMIT 14

Upvotes: 3

Kanishka Panamaldeniya
Kanishka Panamaldeniya

Reputation: 17586

SELECT t.date AS `date`, COUNT(DISTINCT t.user) AS totalcount
FROM
(
    SELECT user, COALESCE(logon,reconnect) AS `date`
    FROM user_logons
) t
GROUP BY t.date
ORDER BY t.date DESC

I have tried to combine Tim Biegeleisen's solution and PM 77-1's solution .

Just tried to help .

Upvotes: 1

Related Questions