I have a table in Postgres named personal_profit as shown below:
| date | profit |
|---|---|
| 2022-09-22 | 4000 |
| 2022-04-25 | 5000 |
| 2022-01-10 | 0 |
| 2022-02-14 | 0 |
| 2022-04-12 | 2000 |
| 2022-05-06 | 1000 |
| 2022-06-13 | 0 |
I want to get total profit ordered by month but starting with the month having profit greater than zero. The query below omits all the records with zero profit which doesn’t satisfy my condition. I just want the query to start from the month 04/2022.
Select distinct date_trunc('month', P.date) as profit_month, SUM(P.profit) as total_profit
from personal_profit P
group by profit_month
having SUM(P.profit) > 0
order by profit_month;
| date | profit |
|---|---|
| 2022-04 | 7000 |
| 2022-05 | 1000 |
| 2022-06 | 0 |
| 2022-09 | 4000 |
>Solution :
Since you want to start where, ordered by date, there is a non-zero value you can simply add that as a where criteria:
Select date_trunc('month', P.date) as profit_month, SUM(P.profit) as total_profit
from personal_profit p
where date >= (select date from personal_profit where profit >0 order by date limit 1 )
group by profit_month
order by profit_month;