List:General Discussion« Previous MessageNext Message »
From:Adam Williams Date:October 28 2009 4:05pm
Subject:help with group by
View as plain text  
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 |
+-------------+---------------------+----------+



Thread
help with group byAdam Williams28 Oct
  • Re: help with group byMichael Dykman28 Oct
    • Re: help with group byAdam Williams28 Oct