0

I'm trying to set 0 to any null value in a LEFT JOIN, using coalesce() or ifnull(), in SQLite. I know this question have been asked a lot, however, I'm stuck. 

BUDGET
amount category
2      food
2      book
2      movie

EXPENSE
amount category
2      food
2      book

EXPECTED RESULT
budsum budcat expsum
2      food   2
2      book   2
2      movie  0

This is the query that I've done.

SELECT SUM(budget.amount) AS budsum, budget.category AS budcat,
SUM(coalesce(expense.amount,0)) AS expsum 
FROM budget 
LEFT JOIN expense 
ON budcat=expense.category
GROUP BY budcat;

SELECT SUM(budget.amount) AS budsum, budget.category AS budcat,
SUM(coalesce(expense.amount,0)) AS expsum 
FROM budget 
LEFT JOIN expense 
ON budcat=expense.category
GROUP BY budcat;
Ahmad Zul
  • 45
  • 10

1 Answers1

0

Use coalesce on the sum instead.

select SUM(budget.amount) as budsum,
    budget.category as budcat,
    coalesce(SUM(expense.amount), 0) as expsum
from budget
left join expense on budget.category = expense.category
group by budget.category;
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76