# Postgresql – calculate or get price of parent items on bill of materials

ctepostgresqlrecursive

i want to calculate or get the price of parent items on bill of materials query

here is the fiddle-> https://www.db-fiddle.com/f/o3jLgZxKNLG14mna8QGdVN/6

please note in the second row, betax has qty = 2 and the other parent items could behave the same

qty of the first row, always will be 1

what i need is to get the 'price' and 'subtotal' for every parent

the price will surely be null if you tell it to. Initialize the base case for the CTE with 0 instead:

with recursive cte_main as
(
select 'ALFA001'::character varying as id_father
, 'ALFA001'::character varying as id_son
, 'ALFA001' as tree
, 1::double precision as qty
, 0::double precision as price
, 0::double precision as subtotal
, 'ALFA001'::character varying as sort
, 0 as levell


Second, you need to handle cases where the table contains null. You can use COALESCE for that purpose:

    union all
select e.id_father
, e.id_son
, repeat('......',levell+1) || e.id_son as tree
, e.qty
, coalesce(e.price,0)
, e.qty * coalesce(e.price,0) as subtotal
, sort || ' a- ' || e.id_son::character varying as sort
, levell+1
from public.main as e
inner join cte_main
on e.id_father = cte_main.id_son
)
select * from cte_main
order by sort


Since all your attributes may contain null, you need to take that into consideration for the other attributes as well.