Kuzuri Kuzuri - 2 months ago 13
SQL Question

Postgresql COALESCE does not set default value

I have two tables:

tcars

id | name | car_price
----|---------------------|------------
1 |First_car_name | 1000
2 |Second_car_name | 1200


tcar_optionals

id | id_car | spec | opt_included |price
----|----------|------|-------------------------
1 | 2 |Spec1 | true | 500
2 | 2 |Spec2 | true | 100
3 | 2 |Spec3 | false | 500
4 | 2 |Spec4 | true | 0
5 | 1 |Spec5 | false | 500
6 | 1 |Spec6 | true | 0


And the following query:

select t1.id, coalesce(t1.car_price, 0)+ coalesce(sum(t2.price), 0) as total_price
from tcars t1
left join tcar_optionals t2 on t2.id_car = t1.id
where t2.opt_included and t2.price>0 and t1.id=?
group by t1.id, t1.car_price


It returns the id from tcars and the total_price(car_price+price of included optionals that have price>0).

Example:

for
t1.id=2
returns:

id | total_price
----|------------
2 | 1800


The problem appears when I have no included optionals with price>0, for example t1.id = 1.

What it returns:

id | total_price
----|------------


What I need is return only t1.car_price as total_price if there are no included optionals with price>0:

id | total_price
----|------------
1 | 1000


Can someone help me with this problem, please?

Answer

select (t1.car_price + coalesce(extra_price, 0)) as start_price
from tcars t1
left join (select id_car,sum(price) as extra_price from tcar_optionals 
where opt_included and price > 0 group by 1) q1 on q1.id_car = t1.id
where t1.id=$1

Comments