hello,
Does someone know why i'm getting this error:


**** the select for t1****
)t1
LEFT JOIN (

SELECT ROUND(ISNULL(sum(p_sum),0),3) AS Total, fk_id AS id
FROM abc
GROUP BY id
)t2 ON t1.id = t2.id


and i'm getting this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0),3) as Total, fk_id as id
from abc
group b' at line 17

thanks!

the ISNULL function takes only one parameter

use this instead --

... ROUND(COALESCE(SUM(p_sum),0),3)

first of all, thank you for the quick response..

I changed it, and Now I don't get an error but I still get NULL instead of zero in the Total column

thanks again..
suami.

Run the subquery on it's own - any NULLs?

I'm assuming the LEFT JOIN is what is introducing the NULL values.

because it's a LEFT OUTER JOIN

do the COALESCE in the outer query

arg, sniped by george again

Sorry but I didn't get you.. if you could please explain what you meant i'll be very thankful..

if you could please post the actual query, i could help you

select * from (SELECT id,
id_contact,
id_address,
name,
total_price,
full_com
FROM sale as s
INNER JOIN click as c ON s.click=c.click
INNER JOIN partner as p ON p._id = b.fk_id
WHERE sale_stat in(1,5)
Group by p.id) t1
LEFT JOIN (
select round(COALESCE(sum(advp_sum),0),3) as Total, fk_id as id
from abc
group by id
) t2 ON t1.id = t2.id


thanks.

Meh .

But total price is not in t2, and I would like to make the column of adv_sum which if it's null the i want zero instead, sorry for keep asking i guess don't explain my self right,

Apologies, mis-read your query.
Change your SELECT * to pick out specific columns. Then apply the Coalesce at that point

this cannot be your real query, because it references a table (b) which doesn't exist


like i said before, do the COALESCE in your outer query

Thank you so much for the help!
got you. worked.