group by + given AND gotten votes in same output
I have table with votes where data is written in this format:
id_user|id_user2|value
1|2|-1
1|3|1
5|3|1
2|1|-1
id_user is user who voted, id_user2 is user who got vote and value represents if he got positive or negative vote. Well and my problem is that i would like to show how stands each user in giving and getting votes in same output. Below is how i would like output from above input would look:
id_user|given|gotten
1|0|-1
2|-1|-1
3|0|2
5|1|0
I don't know how to do this, i just know how i can show this with two separate queries. In this case it would be:
SELECT id开发者_运维问答_user2,SUM(value) FROM `table` GROUP BY id_user2
and
SELECT id_user,SUM(value) FROM `table` GROUP BY id_user
Simply join the queries :
SELECT
gotten.id_user,
gotten.gotten,
given.given
FROM
(
SELECT
id_user2 AS id_user,
SUM(value) AS gotten
FROM
table
GROUP BY
id_user2
) gotten
JOIN -- Join the current row with rows verifying...
(
SELECT
id_user,
SUM(value) AS given
FROM
table
GROUP BY
id_user
) given ON given.id_user = gotten.id_user -- ... this is the same user.
N.B. if the user did not give or did not get votes, it won't return anything for him. If you need a result for these cases, use a full outer join to join even if there are not rows matching your condition in the left or right table.
SELECT
gotten.id_user,
gotten.gotten,
given.given
FROM
(
SELECT
id_user2 AS id_user,
SUM(value) AS gotten
FROM
table
GROUP BY
id_user2
) gotten
FULL OUTER JOIN -- Join the current row with rows verifying...
(
SELECT
id_user,
SUM(value) AS given
FROM
table
GROUP BY
id_user
) given ON given.id_user = gotten.id_user -- ... this is the same user.
You will get null values where there are no given or gotten votes.
精彩评论