Sum function returns only one level of hierarchy

Question

I have table company. It stores the following data: id, title, budget, pid (parent id), total amount. It is necessary to calculate the company's budget + budget of subsidiaries. I do it in the column total.

"SELECT o.id, o.title, o.budget, o.pid, 
(select sum(o1.budget)+o.budget from company o1 where o1.pid=o.id) total
FROM company o ORDER BY o.id";

This request summarizes the amount of budgets only on the level below, and I need to calculate the budgets of all the subordinate companies.

Put `o.budget` in the `sum(o1.budget)` as well `sum(o1.budget+o.budget)` — believe me, Mar 08 '16 at 20:54

score 0 · Answer 1 · answered Mar 09 '16 at 14:19

You are missing a GROUP BY, without the GROUP BY you will only get the total sum of your budget, try something like this:

SELECT o.id, o.title, o.budget, o.pid, 
(select sum(o1.budget)+o.budget from company o1 where o1.pid=o.id) total
FROM company o 
GROUP BY o.id
ORDER BY o.id

Sum function returns only one level of hierarchy

1 Answers1