
-- Update category event count: consider AVAILABLE classes only
UPDATE was_event_category c
JOIN (
  SELECT
  ecr.category_id, COUNT(DISTINCT(ecr.event_id)) as total_count
  FROM was_event_category_relation ecr
  JOIN was_event e ON ecr.event_id = e.event_id
  JOIN wap_r_resource r ON e.event_type_id = r.resource_id
  WHERE r.published = 1
    AND r.disabled = 0
    AND e.event_status = 'COMPLETE'
  GROUP BY ecr.category_id) j ON j.category_id = c.category_id
SET c.event_count = j.total_count;
