MYSQL Multiple column COUNT DISTINCT

Question

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.

In each case is the other date NULL? If so, you can use GROUP BY COALESCE(logon, reconnect) — PM 77-1
– PM 77-1, Commented Jul 9, 2015 at 3:38

PM 77-1 · Accepted Answer · 2015-07-09 03:52:59Z

3

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

answered Jul 9, 2015 at 3:52

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Graham Over a year ago

This works fantastic, and it also cuts the ex time down by a third, by not having a subselect ! Love it !

Tim Biegeleisen Over a year ago

Good call using COALESCE() to handle the missing data.

Kanishka Panamaldeniya · Accepted Answer · 2015-07-09 03:51:42Z

1

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 .

answered Jul 9, 2015 at 3:51

Kanishka Panamaldeniya

17.6k31 gold badges128 silver badges195 bronze badges

2 Comments

PM 77-1 Over a year ago

My solution does not require a sub-query.

Kanishka Panamaldeniya Over a year ago

Yes , that is the best solution :)

Tim Biegeleisen · Accepted Answer · 2015-07-09 04:05:10Z

1

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.

edited Jul 9, 2015 at 4:05

answered Jul 9, 2015 at 3:43

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

Mike Graham Over a year ago

I had to backtick USER but then it worked. I'm looking to imrove the database, as if I could pull session info, then I would not have to store the date in a field. I could have USER DATETIME SESSION EVENT

Collectives™ on Stack Overflow

MYSQL Multiple column COUNT DISTINCT

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related