Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Counting Distinct Values on Multiple Columns

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!)

like image 829
user3140879 Avatar asked Dec 28 '13 00:12

user3140879


People also ask

How do I count distinct values over multiple columns in SQL?

but when we want to count distinct column combinations, we must either clumsily concatenate values (and be very careful to choose the right separator): select count(distinct col1 || '-' || col2) from mytable; or use a subquery: select count(*) from (select distinct col1, col2 from mytable);

Can we use distinct on multiple columns in MySQL?

We can use the DISTINCT clause on more than columns in MySQL. In this case, the uniqueness of rows in the result set would depend on the combination of all columns.

Does distinct apply to multiple columns?

Answer. Yes, the DISTINCT clause can be applied to any valid SELECT query. It is important to note that DISTINCT will filter out all rows that are not unique in terms of all selected columns.

Does distinct apply to all columns?

Yes, DISTINCT works on all combinations of column values for all columns in the SELECT clause.


1 Answers

Just list multiple columns in the GROUP BY clause.

SELECT computer, user, count(*) AS count FROM login GROUP BY computer, user 
like image 190
Barmar Avatar answered Sep 29 '22 02:09

Barmar