Skip to content
Advertisement

Remove zero amount in a query

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 
Advertisement