[rt-users] how to delete user

[Matthew P. Cox]

I'll give you a few select statements. If you can use mysql on the
command line: you can figure out all sorts of stats from your db. These
statements go in order of increasing complexity.

Please don't play with mysql if you really don't know what you're doing.
I accept no responsibility if you screw up your database. :)

If you just want number of tickets of the different status types:

mysql> SELECT rt2.Tickets.Status, COUNT(Status) FROM rt2.Tickets GROUP
BY Status;

Which will give you something like: 

+----------+---------------+
| Status   | COUNT(Status) |
+----------+---------------+
| dead     |            58 |
| new      |            34 |
| open     |            13 |
| resolved |            81 |
| stalled  |             2 |
+----------+---------------+

If you want requesters with dead tickets:

mysql> SELECT u.id, u.RealName, u.EmailAddress, COUNT(*) FROM
rt2.Tickets t, rt2.Users u WHERE t.Status = "dead" and u.id = t.Creator
GROUP BY u.id;

+----+--------------+------------------+----------+
| id | RealName     | EmailAddress     | COUNT(*) |
+----+--------------+------------------+----------+
| 17 | sample luser | sample at luser.com |        2 |
+----+------------------+--------------+----------+

For a list of requesters with a count of the status of the tickets they
own:

mysql> SELECT rt2.Users.id, rt2.Users.EmailAddress, rt2.Tickets.Status,
COUNT(Status) FROM (rt2.Tickets LEFT JOIN rt2.Users ON
rt2.Tickets.Creator=rt2.Users.id) WHERE EmailAddress != "NULL" GROUP BY
EmailAddress, Status ORDER BY id, Status;

Which will give you something like:

+------+------------------+----------+---------------+
| id   | EmailAddress     | Status   | COUNT(Status) |
+------+------------------+----------+---------------+
|    4 | luser1 at luser.com | open     |             1 |
|    4 | luser1 at luser.com | resolved |            13 |
|   12 | luser2 at luser.com | resolved |             1 |
|   17 | luser3 at luser.com | dead     |             2 |
|   17 | luser3 at luser.com | new      |            21 |
|   17 | luser3 at luser.com | open     |             6 |
|   17 | luser3 at luser.com | resolved |             4 |
|   17 | luser3 at luser.com | stalled  |             1 |
+------+------------------+----------+---------------+

To add in time period: modify the above statements adding a WHERE clause
something like:

WHERE rt2.Tickets.Resolved like '2001-%'

Good luck.

Matt

On Sat, 2002-07-06 at 05:17, Anton Krall wrote:
> For instance, is there a way to report all of the tickets in the DB in
> an elegant manner like:
> 
> Total:
> 
> New: X
> Open: Z
> Etc etc