I have the results of a select that I am joining on that I need to get a slightly complex sum of a column from.

I need the sum of result grouped on id PLUS the sum of result of any row where parent_id = id.

The data from the join:

cat_id   parent_id   name    result
------   ---------   -----   ------
1        2           name1   7 
1        2           name1   1
2        4           name2   2

For example from the above I need a result like:

cat_id   name    sum(result)
------   -----   -----------
1        name1   8
2        name2   10

Any ideas much appreciated

  • I would use UNION of 2 queries.May not be the smarted solution, but simple to understand (not sure what your row count is) – Manny Calavera Oct 14 '14 at 23:24
  • Thanks Manny I'll look into it - the row count from the join is about 1,000 – Staple Oct 14 '14 at 23:49
SELECT id, name, SUM(result)
FROM (SELECT cat_id as id, name, result  FROM test
SELECT t1.parent_id as id, t2.name, t1.result 
FROM test as t1 
LEFT JOIN test as t2 ON t2.cat_id = t1.parent_id
) as a 
GROUP BY id,name ORDER BY id
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.