I've written a helpdesk ticket problem and am working on the statistics
module. I'm having problems with group by. For instance, I want to get
the count of the number of different problem types, by how many were
solved by each person. This is my statement:
mysql> select distinct accepted_by, problem_type, count(*) from form
where ((problem_type is not NULL) && (problem_type != 'Test') &&
(accepted_by is not null)) group by problem_type;
+-------------+---------------------+----------+
| accepted_by | problem_type | count(*) |
+-------------+---------------------+----------+
| awilliam | Computer Hardware | 13 |
| awilliam | Computer Peripheral | 16 |
| awilliam | Computer Software | 138 |
| awilliam | Delete User | 4 |
| smccoy | Networking | 17 |
| awilliam | New User | 6 |
| jomiles | Printer | 21 |
| awilliam | Server | 47 |
| sokolsky | Telephone | 6 |
+-------------+---------------------+----------+
9 rows in set (0.00 sec)
But it is leaving out two of the support staff, and smccoy and jomiles
have also solved Computer Software problems, but it's only showing
awilliam as solving Computer Software problems. I think its just
showing accepted_by's values by first occurrence of accepted_by on
problem_type. Here's the two users its not even showing:
mysql> select accepted_by, problem_type, count(*) from form where
(accepted_by = 'ehynum') group by problem_type;
+-------------+---------------------+----------+
| accepted_by | problem_type | count(*) |
+-------------+---------------------+----------+
| ehynum | Computer Peripheral | 1 |
| ehynum | Computer Software | 5 |
| ehynum | Telephone | 1 |
+-------------+---------------------+----------+
3 rows in set (0.00 sec)
mysql> select accepted_by, problem_type, count(*) from form where
(accepted_by = 'dbrooks') group by problem_type;
+-------------+---------------------+----------+
| accepted_by | problem_type | count(*) |
+-------------+---------------------+----------+
| dbrooks | Computer Peripheral | 2 |
| dbrooks | Computer Software | 9 |
| dbrooks | Networking | 2 |
| dbrooks | Printer | 3 |
| dbrooks | Server | 3 |
+-------------+---------------------+----------+
5 rows in set (0.01 sec)
but what I really need is an SQL statement that would return this, but
I'm at a loss as to what that would be:
+-------------+---------------------+----------+
| accepted_by | problem_type | count(*) |
+-------------+---------------------+----------+
| awilliam | Computer Hardware | 6 |
| awilliam | Computer Peripheral | 7 |
| awilliam | Computer Software | 64 |
| awilliam | Delete User | 4 |
| awilliam | Networking | 10 |
| awilliam | New User | 5 |
| awilliam | Printer | 4 |
| awilliam | Server | 33 |
| awilliam | Telephone | 1 |
| awilliam | Test | 1 |
| dbrooks | Computer Peripheral | 2 |
| dbrooks | Computer Software | 9 |
| dbrooks | Networking | 2 |
| dbrooks | Printer | 3 |
| dbrooks | Server | 3 |
| ehynum | Computer Peripheral | 1 |
| ehynum | Computer Software | 5 |
| ehynum | Telephone | 1 |
| jomiles | Computer Hardware | 5 |
| jomiles | Computer Peripheral | 6 |
| jomiles | Computer Software | 44 |
| jomiles | Networking | 1 |
| jomiles | Printer | 12 |
| jomiles | Server | 7 |
| smccoy | Computer Hardware | 2 |
| smccoy | Computer Software | 15 |
| smccoy | Networking | 4 |
| smccoy | New User | 1 |
| smccoy | Printer | 2 |
| smccoy | Server | 4 |
| sokolsky | Computer Software | 1 |
| sokolsky | Telephone | 4 |
+-------------+---------------------+----------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
