How can I tell the sum and count operators to count only unique occurrences?

Question

MySQL

select
  p.id,
  p.author author_id,
  p.publish_date,
  p.views,
  sum(v.value) rating,
  count(case when v.value > 0 then 1 end) likes,
  count(case when v.value < 0 then 1 end) dislikes,
  p.title,
  coalesce(p.content_full, p.content_brief) content,
  group_concat(distinct td.name) tags,
  count(distinct c.id) comments,
  u.name author_name
from
  posts p
  left join tags t on t.post_id = p.id
  left join tags_data td on td.id = t.tag_id
  left join votes v on v.type = 'article' and v.content_id = p.id
  left join comments c on c.post_id = p.id
  left join users u on u.id = p.author
where p.id = 4
group by p.id

I have such a request to get a post and related data - tags, author name, number of comments and rating. Ok,
count(distinct c.id) comments
allows you to correctly count the comments. I'm messing with the rating and like-dislike count: they are multiplied by the number of related sections, and I don't know how to tell the sum and count operators to count only unique matches in the same way as I did with the comment count.
Rather, I know

cast(sum(v.value)/count(v.id)*count(distinct v.id) as signed) rating,
cast(count(case when v.value > 0 then 1 end)/count(v.id)*count(distinct v.id) as unsigned) likes,
cast(count(case when v.value < 0 then 1 end)/count(v.id)*count(distinct v.id) as unsigned) dislikes

, but I hope that there is a less crutch way.

Answer 1

I don't remember if it's possible to do 2 joins on the same table

LEFT JOIN `votes` AS v ON  (v.`type` = 'article' AND v.`content_id` = p.`id` AND v.`value` < 1);
LEFT JOIN `votes` AS z ON  (z.`type` = 'article' AND z.`content_id` = p.`id` AND z.`value` > 0);

Well, the corresponding accounts for them
Show the table of likes