count records when another ecord exists

[thompom]

Hi - am struggling making this work

Have a list of users and a count when a record action.typeid = 1 and a response.reasonid LIKE appointment

IF

a record with action.typeid = 2 exists with actiondate between two dates

the two action records are linked by table Event
all dealcount show as "1" however if i run the sub-query
on its own - the totals are shown

so far...

SELECT 
  users.userid,
  users.userf,
  users.userl,
  COALESCE(dealcount.dealcount,0) AS dealcount 
FROM
  users  
  LEFT JOIN 
    (SELECT 
      COUNT(
        CASE
          WHEN UCASE(response.reasonid) LIKE '%APPOINTMENT%' 
          THEN action.actionid 
        END
      ) AS dealcount,
      response.createuserid 
    FROM
      EVENT 
      LEFT JOIN ACTION 
        ON action.eventid = event.eventid 
      INNER JOIN response 
        ON action.actionid = response.actionid 
    GROUP BY event.eventid 
    HAVING COUNT(
        CASE
          WHEN action.typeid = '2' 
          AND DATE(action.actiondate) BETWEEN '2012-01-01' 
          AND '2012-01-31' 
          THEN 'count' 
        END
      ) > 0) AS dealcount 
    ON dealcount.createuserid = users.userid 
GROUP BY users.userid 
ORDER BY dealcount.dealcount DESC