Move the where clause on cashvalue2 to the join. Also, account for cashvalue2 being null - see changes in red below.INSERT INTO accountvalues
(
account,
TYPE,
date,
cashreturn,
cashvalue
)
SELECT a.account,
'totalcash' AS TYPE,
a.date,
a.cashreturn,
SUM(a.cashvalue + COALESCE(b.cashvalue,0)) AS cashvalue
FROM accountvalues a
LEFT JOIN accountvalues b
ON a.account = b.account
AND a.date = b.date
AND b.type = 'cashvalue2'
WHERE a.type = 'cashvalue1'
Join two records
Posted - 09/30/2012 : 16:35:41
