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
Advertisement
Answer
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