How can I count the number of all votes per voter?

Question

Here is my table structure:

// users
+----+--------+
| id |  name  |
+----+--------+
| 1  | Jack   |
| 2  | Peter  |
| 3  | John   |
| 4  | Barman |
| 5  | Ali    |
+----+--------+

// vote
+----------+---------------+---------+
| voter_id | owner_post_id | post_id |
+----------+---------------+---------+
| 2        | 3             | 1653    |
| 4        | 2             | 1214    |
| 1        | 1             | 4355    |
| 4        | 2             | 6445    |
| 2        | 2             | 5465    |
| 3        | 2             | 5435    |
+----------+---------------+---------+

And here is my current query:

SELECT t2.id AS user_id,
       t2.name AS user_name,
       t3.id AS voter_id,
       t3.name AS voter_name
FROM vote t1
INNER JOIN users t2
    ON t1.owner_post_id = t2.id
INNER JOIN users t3
    ON t1.voter_id = t3.id
WHERE t1.owner_post_id = 2 AND
      t1.voter_id <> t1.owner_post_id

And here is my current output:

+---------+-----------+----------+------------+
| user_id | user_name | voter_id | voter_name |
+---------+-----------+----------+------------+
| 2       | Peter     | 4        | Barman     |
| 2       | Peter     | 4        | Barman     |
| 2       | Peter     | 3        | John       |
+---------+-----------+----------+------------+

---

Now I want to add one more column to the result which contains the total number of votes per voter. So this is expected result:

+---------+-----------+----------+------------+-----------+
| user_id | user_name | voter_id | voter_name | total_num |
+---------+-----------+----------+------------+-----------+
| 2       | Peter     | 4        | Barman     | 2         |
| 2       | Peter     | 3        | John       | 1         |
+---------+-----------+----------+------------+-----------+

How can I do that?

Answer 1

Get all votes for the user, aggregate them and count. Then join the user table twice; once for the user, once for the voter.

select
  usr.id as user_id, 
  usr.name as user_name, 
  vtr.id as voter_id, 
  vtr.name as voter_name,
  v.cnt as total_num
from 
(
  select owner_post_id, voter_id, count(*) as cnt
  from vote
  where owner_post_id = 2 and owner_post_id <> voter_id
  group by owner_post_id, voter_id
) v
join user usr on usr.id = v.owner_post_id
join user vtr on usr.id = v.voter_id;

And here is the same method applied to your real query. Just some other table and column names, so I think you should have been able to do this yourself.

select 
  pr1.id as user_id,
  pr1.title as user_name,
  pr2.id as liker_id,
  pr2.title as liker_name,
  x.which as which_table,
  x.cnt as total
from 
(
  select rid, rootid, which, count(*) as cnt
  from
  (
    select rid, rootid, 'vote' which from p_likes
    union all 
    select rid, rootid, 'comment' which from p_comments
    union all 
    select rid, rootid, 'friend' which from relations
  ) all_in_one
  where rootid = 1 and rootid <> rid
  group by rid, rootid, which
) x
join pagesroot pr1 on x.rootid = pr1.id
join pagesroot pr2 on x.rid = pr2.id
order by x.which;

Answer 2

SELECT t2.id AS user_id,
       t2.name AS user_name,
       t3.id AS voter_id,
       t3.name AS voter_name
       Count(t1.voter_id) AS total_num
FROM vote t1
INNER JOIN users t2
    ON t1.owner_post_id = t2.id
INNER JOIN users t3
    ON t1.voter_id = t3.id
WHERE t1.owner_post_id = 2 AND
      t1.voter_id <> t1.owner_post_id
GROUP BY t2.id AS user_id,
       t2.name,
       t3.id,
       t3.name

EDIT:

SELECT pr1.id AS user_id,
       pr1.title AS user_name,
       pr2.id AS liker_id,
       pr2.title AS liker_name,
       x.which AS which_table,
       COUNT(pr1.id) AS total
FROM (
SELECT rid, rootid, 'vote' which FROM p_likes
UNION ALL 
SELECT rid, rootid, 'comment' which FROM p_comments
UNION ALL 
SELECT rid, rootid, 'friend' which FROM relations
) x
INNER JOIN pagesroot pr1
      ON x.rootid = pr1.id
INNER JOIN pagesroot pr2
      ON x.rid = pr2.id
WHERE x.rootid = 1
      AND x.rootid <> x.rid
GROUP BY pr1.id,
       pr1.title,
       pr2.id,
       pr2.title,
       x.which
ORDER BY x.which