Count users with a condition in MYSQL-CodePudding

I hope this makes sense...

I have a table 'helpdesk' (detail below) that lists tickets.

I'm trying to get the top 10 users who report issues, however there is a complication in the fact that users can report an issue for another user (think secretary for their boss as an example).

The table structure is...

| hd_id | hd_user | hd_forMe | hd_reportedFor |
|   1   |    1    |    1     |        0       |
|   2   |    1    |    1     |        0       |
|   3   |    2    |    1     |        0       |
|   4   |    3    |    0     |        4       |

Using the example above the count should return

User 1 = 2 tickets (rows 1 & 2)
User 2 = 1 ticket (row 3)
User 3 = 0 (because they reported it for user 4)
User 4 = 1 ticket (row 4 as reported by user 3)

I can't seem to get my head around it.

I've tried using an IF in the count(If(hd_forMe = 1, 1, 0)) and a CASE statement but this doesn't then count the record for the user referenced in the hd_reportedFor column. I've also tried various combinations in the WHERE but can't quite get that either.

These are some of the queries I've tried. The people table lists their names etc. and isn't really important to the scope of this question.

SELECT count(IF(hd_forMe = 1, 1, 0)) AS count, hd_user, people.people_firstName, 
people.people_lastName 
FROM helpdesk
LEFT JOIN people ON people.people_id = helpdesk.hd_user
GROUP BY `hd_user` 
ORDER BY count DESC
LIMIT 10

And

SELECT count(*), people.people_firstName, people.people_lastName
FROM helpdesk
LEFT JOIN people ON people.people_id = helpdesk.hd_user OR people.people_id = helpdesk.hd_reportedFor
WHERE helpdesk.hd_reportedFor != 0 
GROUP BY people.people_id

CodePudding user response：

Assuming that people_id is the primary key of people you should do a LEFT join of people to helpdesk (and not the other way around) and use a CASE expression in the ON clause, so that you pick the correct user:

SELECT p.people_id, 
       p.people_firstName,
       p.people_lastName,
       COUNT(h.hd_id) AS count
FROM people p LEFT JOIN helpdesk h 
ON p.people_id = CASE WHEN h.hd_reportedFor = 0 THEN h.hd_user ELSE h.hd_reportedFor END
GROUP BY p.people_id 
ORDER BY count DESC
LIMIT 10;