ALTER TABLE `wap_r_resource` ADD `rating_avg` DECIMAL(3,1) NOT NULL DEFAULT '00.0' AFTER `last_comment_date`;
ALTER TABLE `wap_r_resource` ADD `rating_count` INT(11) unsigned NOT NULL DEFAULT 0 AFTER `rating_avg`;

ALTER TABLE `was_event` ADD `participant_count` INT(11) unsigned NOT NULL DEFAULT 0 AFTER `ustream_video_id`;

ALTER TABLE `was_event_category` ADD `event_count` INT(11) unsigned NOT NULL DEFAULT 0 AFTER `category_name`;


UPDATE wap_r_resource as r1
JOIN (
  SELECT wr.type_id, wr.type, COUNT(wr.review_id) as rating_count, AVG(wr.rating) as avg_rating
  FROM wa_review wr
  GROUP BY wr.type_id
) x ON x.type_id = r1.resource_id AND x.type = 'RESOURCE'
SET r1.rating_avg = x.avg_rating, r1.rating_count = x.rating_count

UPDATE was_event as e
JOIN (
  SELECT ep.event_id, COUNT(DISTINCT(ep.user_id)) as total_count
  FROM was_event_participant ep
  GROUP BY ep.event_id
) x ON x.event_id = e.event_id
SET e.participant_count = x.total_count;


UPDATE was_event_category as c
JOIN (
  SELECT ecr.category_id, COUNT(DISTINCT(ecr.event_id)) as total_count
  FROM was_event_category_relation ecr
  GROUP BY ecr.category_id
) x ON x.category_id = c.category_id
SET c.event_count = x.total_count;
