SELECT posts.*, SUM(IF(comments.status='new',1,0)) as count_comments_new, count(comments.id) as count_comments_total FROM posts LEFT JOIN comments ON posts.id=comments.post_id WHERE ((posts.title LIKE '%asd%') OR (posts.body LIKE '%asd%')) AND posts.site_id = 3 GROUP BY posts.id
Вот так все заработало! Не забывайте про скобочки
