try this: select accepted_by, problem_type, count(*) from form where problem_type is not NULL AND problem_type != 'Test' AND accepted_by is not null group by accepted_by, problem_type
On Wed, Oct 28, 2009 at 12:05 PM, Adam Williams <awill...@mdah.state.ms.us> wrote: > 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=mdyk...@gmail.com > > -- - michael dykman - mdyk...@gmail.com "May you live every day of your life." Jonathan Swift -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
