It will be like:

SELECT COUNT(`sys_profiles_conn_subscriptions`.`id`) AS `value` FROM `sys_profiles_conn_subscriptions` INNER JOIN `sys_profiles` ON `sys_profiles`.`id` = `sys_profiles_conn_subscriptions`.`content` WHERE sys_profiles.type='bx_persons' and `sys_profiles`.`content_id`=[given profile ID] GROUP BY `sys_profiles`.`content_id`, `sys_profiles`.`type`