MySQL join and sum issue / combine queries

Question

I have created a points example to demonstrate my issue.

A members can reward other members points, I am trying to build a query which will display the points a user has and points user has given.

Database Example

Members,

+----+----------+
| id | username |
+----+----------+
|  1 | user1    |
|  2 | user2    |
|  3 | user3    |
+----+----------+

Points,

+-------+--------+--------+
| IDFor | IDFrom | Pointz |
+-------+--------+--------+
|     1 |      2 |      5 |
|     1 |      2 |      5 |
|     3 |      1 |      2 |
+-------+--------+--------+

The return I am looking for is,

+-----------+--------+-------+
| username  | Pointz | Given |
+-----------+--------+-------+
| user1     |     10 |     2 |
| user2     |      0 |    10 |
| user3     |      2 |     0 |
+-----------+--------+-------+

Both my queries return,

+-----------+--------+-------+
| username  | Pointz | Given |
+-----------+--------+-------+
| user1     |     10 |     4 |
| user2     |      0 |    10 |
| user3     |      2 |     0 |
+-----------+--------+-------+

http://sqlfiddle.com/#!2/32ae6/6

SELECT a.`username`, sum(a.`Pointz`), sum(b.`Pointz`) FROM
  (SELECT * FROM `members`
LEFT JOIN  `Example`.`Points` AS p ON `members`.`id` = p.`IDFor` ) AS a
LEFT JOIN

  (SELECT * FROM `members` 
LEFT JOIN  `Example`.`Points` AS n ON `members`.`id` = n.`IDFrom` ) AS b
ON a.id = b.id
GROUP BY  a.`id`

http://sqlfiddle.com/#!2/32ae6/7

SELECT `members`.`username`,sum(p.`Pointz`),sum(n.`Pointz`)
FROM `members`
LEFT JOIN `Example`.`Points` as p ON p.`IDFor` = `members`.`id`
LEFT JOIN `Example`.`Points` as n ON n.`IDFrom` = `members`.`id`
GROUP BY `members`.`id`

Seems to be a common question that pops up but have not found a solution, all my other attempts from similar questions have not ended well, Thanks all.

Kapil Jain · Accepted Answer · 2013-08-18 11:20:15Z

1

This is what u want

select a.username, ifnull(Pointz, 0) Pointz, ifnull(Given, 0) Given from
  (SELECT id, `username`, sum(`Pointz`) Pointz FROM `members` 
  LEFT JOIN `Points` ON `members`.`id` = `Points`.`IDFor` group by id) a 
  left join
  (SELECT id, `username`, sum(`Pointz`) Given FROM `members` 
  LEFT JOIN `Points` ON `members`.`id` = `Points`.`IDFrom` group by id) b
  on a.id = b.id

edited Aug 18, 2013 at 11:20

answered Aug 18, 2013 at 10:56

Kapil Jain

1881 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2693539 Over a year ago

WOW, tested before the edit, implemented into my project and worked a treat. will check out your edit, shortly :)

piotrm · Accepted Answer · 2013-08-18 10:57:38Z

1

Try:

SELECT m.username, 
       COALESCE( pr.preceived, 0 ) as Pointz,
       COALESCE( pg.pgiven, 0 ) as Given

FROM members m

LEFT JOIN ( SELECT IDFor, sum(pointz) as preceived 
            FROM Points 
            GROUP BY IDFor ) pr
ON m.id = pr.IDFor

LEFT JOIN ( SELECT IDFrom, sum(pointz) as pgiven 
            FROM Points 
            GROUP BY IDFrom ) pg
ON m.id = pg.IDFrom

http://sqlfiddle.com/#!2/32ae6/48

answered Aug 18, 2013 at 10:57

piotrm

12.4k5 gold badges33 silver badges28 bronze badges

Comments

krokodilko · Accepted Answer · 2013-08-18 11:06:22Z

1

Try this:

SELECT m.`username`, 
       ifnull((SELECT sum(`Pointz`) FROM Points p
        WHERE p.IDFor = m.id ), 0),
       ifnull((SELECT sum(`Pointz`) FROM Points p
        WHERE p.IDFrom = m.id ), 0)
FROM Members m

SQLFiddle

answered Aug 18, 2013 at 11:06

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Collectives™ on Stack Overflow

MySQL join and sum issue / combine queries

Database Example

http://sqlfiddle.com/#!2/32ae6/6

http://sqlfiddle.com/#!2/32ae6/7

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Database Example

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related