I have a table finalData with the following columns and data
| Code | Country | City | Area | Amount | Date |
| 4000 | Singapore | Singapore | 1 | 150000.00 | 2020-11-30 |
| 4004 | Singapore | Singapore | 1 | 355000.00 | 2020-11-30 |
| 4005 | Singapore | Singapore | 2 | 0.00 | 2020-11-30 |
| 5001 | Singapore | Singapore | 1 | 13600.00 | 2020-11-30 |
I am creating mysql query to display the income and expenses of Singapore per Area, but I have to exclude areas that has zero amount. Here's the code I tried:
Select fd.Area as area,
FORMAT((Select IFNULL(SUM(Amount),0) from finalData Where Code LIKE '4%' AND fd.City=finalData.City AND finalData.Area=fd.Area),2) as Revenue,
FORMAT((Select IFNULL(SUM(Amount),0) from finalData Where Code LIKE '5%' AND fd.City=finalData.City AND fd.Area=finalData.Area),2) as Expense
from finalData fd WHERE fd.Date BETWEEN '2020-11-30' AND '2020-11-30' AND fd.City='Singapore' AND fd.Amount <>0 GROUP BY fd.Area
Output should be like this
| Area | Revenue | Expense |
| 1 | 505,000.00 | 13,600.00 |
Any help would be appreciated. Thanks
This logic would be simpler phrased with conditionl aggregation. As for filtering out 0 amounts, I am speculating that you just want a where clause:
select area,
sum(case when code like '4%' then amount else 0 end) as revenue,
sum(case when code like '5%' then amount else 0 end) as expense
from finaldata fd
where fd.city = 'Singapore' and amount <> 0
group by area
If, on the other hand, you want to filter on one of the two amounts, then you can use a having clause. Say you want to filter out 0 revenues, regardless of the expenses, then would add the following expression at the end of your code:
having revenue <> 0