1

I'm having trouble coming up with a query in MySQL.

Suppose we have a table that logs an action by a user.

  • User1 is a Doctor and is logged several times.
  • User2 is a Doctor and is logged once.
  • User3 is an Accountant and is logged twice.
  • User4 is a Clerk and hasn't been logged.
  • User5 is an Accountant and hasn't been logged.

I would like to know what "roles" (i.e., doctor, accountant, clerk) have entries in the log file.

Here is the schema:

CREATE TABLE `test1_users` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `role` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `test1_log` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

And test data:

INSERT INTO `test1_users` VALUES
(1, 'Franklin', 'Doctor'),
(2, 'Sally', 'Doctor'),
(3, 'Linus', 'Accountant'),
(4, 'Patty', 'Clerk'),
(5, 'Marcie', 'Accountant');

INSERT INTO `test1_log` VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 1),
(5, 3),
(6, 3);

If I want to see how many times a user has been logged, it's pretty straight forward.

SELECT u.name, COUNT(*) AS total
FROM test1_log l
LEFT JOIN test1_users u ON u.id = l.user_id
GROUP BY user_id;

The query produces the following:

+----------+-------+
| name     | total |
+----------+-------+
| Franklin |     3 |
| Sally    |     1 |
| Linus    |     2 |
+----------+-------+

But how would I structure the query if I want to see how many different "roles" have been logged? Not how many times each role is logged, just a list of all the roles that are logged.

Specifically, how many different Doctors are logged? I don't care how many times each has been logged.

Such a query I'm looking for would produce the following output:

+------------+-------+
| role       | total |
+------------+-------+
| Doctor     |     2 |
| Accountant |     1 |
| Clerk      |     0 |
+------------+-------+

Both Doctors were logged, but only one Accountant and no Clerks.

Here's the closest I've come:

SELECT role, COUNT(l.id) AS total FROM test1_users u 
LEFT JOIN test1_log l ON u.id = l.user_id 
GROUP BY role;

But this only gives me the number of log entries, and I need the number of roles that made it into the log.

Thoughts?

Thanks.

1

By my reading of your question, you want the numbers of distinct users who have been logged, broken down by each user's role.

Your query returns the number of log entries, broken down by role.

To get distinct users, we'd need to get a COUNT(DISTINCT x) on some column x, which represents users.

If we used a column from test1_users, we'd just get the count of users assigned to each role, without reference to whether or not the user had associated entries in the log.

If we use an INNER JOIN, we'll only count users who do have entries in the log - but we won't see any roles where no user exists in the log (as is true for "Clerk").

However, there's a columns in test1_log that's specific to a user - test1_log.user_id.

So, if we modify the query to be:

SELECT role, COUNT(DISTINCT l.user_id) AS total FROM test1_users u 
LEFT JOIN test1_log l ON u.id = l.user_id 
GROUP BY role;

we get the results you request (as you can see in this SQLFiddle.

0

You want to know the number of distinct roles recorded in a given period?

SELECT
  COUNT(DISTINCT U.Role) AS NumRolesRecorded
FROM
  test1_log AS Lg
  INNER JOIN test1_users AS U ON Lg.User_ID = U.ID

The result should be "2". The use of COUNT DISTINCT for MySQL is addressed on Stack Exchange, if you'd like additional detail.

  • Thank you for your answer. I'm actually looking for a list of distinct roles, not a count. – pbarney Jul 3 '18 at 12:56
  • So... exactly what you posted, but without the total column? If so, I don't see what the question is; just don't include it. If not, you should probably clarify. A good practice is to give an example of the output you'd like to see. – Jon of All Trades Jul 3 '18 at 13:14
  • Thanks Jon, I actually did. Look for "query I'm looking for would produce the following output:" – pbarney Jul 3 '18 at 15:06
  • My interpretation was that that result was what you were getting, with your SELECT role... query, but I suppose not. Is your sticking point that you want a list of all roles, and a count with the number of times each one appeared, which may be zero? If you don't need the zeros, the query is utterly simple, you're just grouping by role rather than user name, and there's really no question. But perhaps I've over-complicated what you're asking; is it really all about "these are all the roles; how many (if any) appear in the log"? – Jon of All Trades Jul 3 '18 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.