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
<awilliam@stripped> 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=1
>
>
--
- michael dykman
- mdykman@stripped
"May you live every day of your life."
Jonathan Swift
