MySQL Counting Distinct Values on Multiple Columns

Question

I wrote a script that runs each time a user logs into a computer in our domain. This script makes a record of the user as well as the computer they logged into. Any number of users can log into any number of computers.

I just inherited this IT environment from a consultant who is no longer around, and I'm writing this little query so when I get a call from a user, I can search by that user's name and reasonably predict which computer they are using by the number of times they've logged into any given computer.

Here's a sample of the data in the 'login' table:

    COMPUTER        USER
    ncofp02         lee
    ncofp02         lee
    ncofp02         andy
    ncodc01         andy
    ncodc01         andy
    ncodc01         lee

What I'm banging my head on is the logic to count distinct values across multiple columns. I'd like to see a result like this:

    COMPUTER       USER   COUNT
    ncofp02        lee    (2)
    ncofp02        andy   (1)
    ncodc01        lee    (1)
    ncodc01        andy   (2)

Is there a way to accomplish this with a single query within mysql, or should I start looping some php? (booooo!)

Barmar · Accepted Answer · 2013-12-28 00:22:37Z

65

Just list multiple columns in the GROUP BY clause.

SELECT computer, user, count(*) AS count
FROM login
GROUP BY computer, user

answered Dec 28, 2013 at 0:22

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

1 Comment

user3140879 Over a year ago

Thank you! I can't believe it was seriously THAT easy. LOL I guess I must have been overthinking. Thanks again!

Saharsh Shah · Accepted Answer · 2013-12-28 05:52:46Z

7

Try this:

SELECT l.computer, l.user, COUNT(DISTINCT l.computer, l.user) AS count
FROM login l 
GROUP BY l.computer, l.user

answered Dec 28, 2013 at 5:52

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

1 Comment

Barmar Over a year ago

You don't need DISTINCT if you're counting the same thing you're grouping by.

Snaver · Accepted Answer · 2018-12-04 13:44:17Z

3

Easy!

SELECT 
    computer, user, COUNT(DISTINCT computer, user) AS count
FROM 
    login

answered Dec 4, 2018 at 13:44

Snaver

4473 silver badges7 bronze badges

Collectives™ on Stack Overflow

MySQL Counting Distinct Values on Multiple Columns

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related