johnnyaction
asked on
SQL Syntax question #10
I have this query and I need to return 0 for the nested queries results for 'requests' and 'total amount'. I have used ISNULL but I still get back blank values. Can anyone help.
SELECT *
FROM AuditorTemp a
LEFT JOIN (
select users.id as UserID, users.first_name + ' ' + users.last_name as Name, count(*) as 'requests', ISNULL(sum(u.amount),0) as 'total amount'
from takeoff_requests t, takeoff_request_accounts tra, accounts_master am, underpayments u, users
where t.manager_action = 'complete'
and t.id = tra.takeoff_request_id
and tra.account_id = am.account_id
and tra.account_id = u.account_id
and u.user_id = users.id
group by users.id, users.last_name, users.first_name
) b ON a.id = b.userid
Order by a.id
SELECT *
FROM AuditorTemp a
LEFT JOIN (
select users.id as UserID, users.first_name + ' ' + users.last_name as Name, count(*) as 'requests', ISNULL(sum(u.amount),0) as 'total amount'
from takeoff_requests t, takeoff_request_accounts tra, accounts_master am, underpayments u, users
where t.manager_action = 'complete'
and t.id = tra.takeoff_request_id
and tra.account_id = am.account_id
and tra.account_id = u.account_id
and u.user_id = users.id
group by users.id, users.last_name, users.first_name
) b ON a.id = b.userid
Order by a.id
ASKER
unfortunately that didnt work either. It doesnt even come back as Null because of the SUM and COUNT I guess. It just comes back blank. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you that worked for me...
ISNULL(sum(u.amount),0) as 'total amount'
Should perhaps be
Sum(IsNull(u.Amount,0)) As 'total amount'
You are replacing the final sum with zero if it is a null whereas you probably need to treat individual records with a null as zero.
Regards,
TimCottee