Show the amount of tickets resolved by users.

-- Tickets resolved per user
SELECT t.count, u.name, u.realname FROM
       (SELECT creator, COUNT(creator) AS count
               FROM transactions
               WHERE objecttype = 'RT::Ticket'
                       AND type = 'Status'
                       AND newvalue = 'resolved'
               GROUP BY creator) AS t
       LEFT JOIN users u ON t.creator = u.id;

---

Changed as table names are beginning with capital letters.

SELECT t.count, u.name, u.realname FROM
      (SELECT creator, COUNT(creator) AS count
              FROM Transactions
              WHERE objecttype = 'RT::Ticket'
                      AND type = 'Status'
                      AND newvalue = 'resolved'
              GROUP BY creator) AS t
      LEFT JOIN Users u ON t.creator = u.id;

---

-- NB: Will not work as writen on mySQL. Ended up having to do following:

CREATE TEMPORARY TABLE t  SELECT Creator, COUNT(Creator) as count;
SELECT t.count, u.name, u.realname FROM t LEFT JOIN Users u ON t.Creator = u.id;

Alternate PHP method would be to store results into arrays and assemble at presentation layer.

- MichaelErana, CTO PC Network Inc.

---

Update: The above (original without using temporary table) works on MySQL 5.0.18.

- Bill R. Williams, ETSU Library Systems

---

--Query as above but including date range limit and total time worked (August 2, 2005)

CREATE TEMPORARY TABLE t SELECT Owner, COUNT(Owner) as count, SUM(TimeWorked)/60 AS TotalTime
From Tickets
where Resolved>'2005-07-01'
AND Resolved<'2005-07-31'
GROUP By Owner;
SELECT t.count, u.realname,  t.TotalTime
FROM t
LEFT JOIN Users u ON t.Owner = u.id
GROUP by t.Owner
ORDER by COUNT Desc;

NB: If you run this more than once in a session, you'll need to drop the temp table before the next run.

DROP TEMPORARY TABLE t;

- MichaelErana, CTO PC Network Inc.