List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:October 28 2009 4:23pm
Subject:Re: help with group by
View as plain text  
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
Thread
help with group byAdam Williams28 Oct
  • Re: help with group byMichael Dykman28 Oct
    • Re: help with group byAdam Williams28 Oct