Vahid Alvandi Vahid Alvandi - 4 months ago 11
SQL Question

sum price of childs in other table mysql

I have two table one store data child and parent hierarchy and other paths and descendant

+----------+------------+-----------+
| userid | parent | price |
+----------+------------+------------
| 1 | null | 20 |
| 2 | 1 | 20 |
| 3 | 1 | 20 |
| 4 | 2 | 20 |
| 5 | 2 | 20 |
| 6 | 3 | 20 |
| 7 | 4 | 20 |
+----------+------------+-----------+


I need to get all userid with parent 1 then get descendant in other table and group by userid sum prices

+-------------+---------------+-------------+
| ancestor_id | descendant_id | path_length |
+-------------+---------------+-------------+
| 1 | 1 | 0 |
| 1 | 2 | 1 |
| 1 | 3 | 1 |
| 1 | 4 | 2 |
| 1 | 5 | 2 |
| 1 | 6 | 2 |
| 1 | 7 | 3 |
| 2 | 2 | 0 |
| 2 | 4 | 1 |
| 2 | 5 | 1 |
| 2 | 7 | 2 |
| 3 | 3 | 0 |
| 3 | 6 | 1 |
| 4 | 4 | 0 |
| 4 | 7 | 1 |
| 5 | 5 | 0 |
| 6 | 6 | 0 |
| 7 | 7 | 0 |
+-------------+---------------+-------------+


I have query it sum all childs together

select
sum(b.price)

from webineh_prefix_nodes_paths_tmp a

join webineh_prefix_nodes_tmp b on (b.userid = a.descendant_id)

where a.ancestor_id = 1


this work fine but total sum parent 1

I need to show bellow result for child direct (2,3)

+----------+------------+-
| userid | total |
+----------+------------+
| 2 | 80 |
| 3 | 40 |
+----------+------------+


also in create sqlfiddle my question http://sqlfiddle.com/#!9/9415ed/2

Answer

Try this;)

select ancestor_id as userid, sum(b.price) as total
from webineh_prefix_nodes_paths_tmp a 
join webineh_prefix_nodes_tmp b 
on b.userid = a.descendant_id
where a.ancestor_id in (select userid from webineh_prefix_nodes_tmp where parent = 1)
group by ancestor_id

SQLFiddle Demo

Edited

select ancestor_id as userid, sum(b.price) as total
from webineh_prefix_nodes_paths_tmp a 
join webineh_prefix_nodes_tmp b 
on b.userid = a.descendant_id
inner join webineh_prefix_nodes_tmp c
on a.ancestor_id = c.userid
and c.parent = 1
group by ancestor_id

SQLFiddle Demo

Comments