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
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
people_id is the primary key of
people you should do a
LEFT join of
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;