title: Having count() greater than average of
body: <p>I'm trying to resolve the following SQL query which it's supposed to show the equipment data that had more problems this year than the average amount of problems of all equipments in the past year and i'm a little bit stuck with it.</p>
  
  <p>I've come up with this so far:</p>
  
  <pre><code>select e.equipment_id ,
         e.equipment_name ,
         e.equipment_serialnum ,
         e.equipment_descrip
  from equipment e ,
       problems  p
  where e.equipment_id       = p.equipment_id
    and year( problem_date ) = year( getdate() )
  group by e.equipment_id   ,
           e.equipment_name ,
           e.equipment_serialnum ,
           e.equipment_descrip
  having count(p.problem_id) &gt; ( select avg( pnumber )
                                 from ( SELECT p1.problem_id ,
                                               COUNT( p1.problem_id ) AS pnumber
                                        FROM problems  p1
                                        JOIN equipment e1 ON e1.equipment_id = p1.equipment_id
                                                         and year( p1.problem_date ) = 2013
                                        GROUP BY p1.problem_id
                                      )
  </code></pre>
  
  <p>but it's not working. This is the structure of the tables involved in the query</p>
  
  <pre><code>problems
  (
    problem_id      integer  not null identity(1,1)
    equipment_id    integer  not null,
    user_id         integer  not null,
    problem_date    datetime not null,
    problem_descrip varchar(255)
  )
  
  equipment
  (
    equipment_id        integer    not null identity(1,1) ,
    equipment_type_cod  char(20)   not null ,
    equipment_bought    datetime   not null ,
    equipment_available datetime   ,
    equipment_serialnum char(20)   ,
    equipment_name    varchar(255) ,
    equipment_descrip varchar(255)
  )
  </code></pre>
  
  <p>I appreciate any help that you can provide.</p>