SQL to omit initial records until a column value is greater than zero

Question

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

No need to SELECT DISTINCT, your GROUP BY has already eliminated the duplicates. — jarlh, Mar 12 '23 at 21:18

Stu · Accepted Answer · 2023-03-12T21:50:25.993

3

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;

demo fiddle

edited Mar 12 '23 at 21:50

answered Mar 12 '23 at 21:41

Stu

30,392
6
14
33

1

Well, this is the simplest approach, IMHO. Not adding unnecessary queries after `group by`. I believe you can remove the `distinct` since we have a `group by`. – Raging Bull Mar 12 '23 at 21:50
1

Yes of course - I did actually but copied the OP's original statement and accidentally left it in; have removed. – Stu Mar 12 '23 at 21:51
I was thinking of a far more complex solution, when something as simple as this does the trick perfectly. – The Impaler Mar 12 '23 at 22:43
Should perform excellently even on a big table with an index on `(date, profit)`. Let me just suggest to use `date_trunc('month', p.date::timestamp)` instead. Else, the `date` is coerced to `timestamptz` and that drags in the time zone setting of the current session. Nothing breaks in this simple query, but it adds a bit of cost and complication for no benefit at all. See: https://stackoverflow.com/a/46499873/939860 – Erwin Brandstetter Mar 13 '23 at 00:24

Tomáš Záluský · Answer 2 · 2023-03-12T21:41:26.077

The condition "starting with the month having profit greater than zero" essentially means "skip the longest subsequence of zeros from the beginning". This can be achieved via selecting months with nonzero running total or via subquery. I consider the first way more comprehensible:

with personal_profit (date,profit) as (values
  (date '2022-09-22', 4000),
  (date '2022-04-25', 5000),
  (date '2022-01-10', 0),
  (date '2022-02-14', 0),
  (date '2022-04-12', 2000),
  (date '2022-05-06', 1000),
  (date '2022-06-13', 0)
)
, grouped as (
  select date_trunc('month', P.date) as profit_month
       , sum(P.profit) as total_profit
       , sum(sum(P.profit)) over (order by date_trunc('month', P.date)) as running_total
  from personal_profit P
  group by profit_month
)
select profit_month, total_profit
from grouped
where running_total > 0
order by profit_month;

profit_month	total_profit
2022-04-01 00:00:00+01	7000
2022-05-01 00:00:00+01	1000
2022-06-01 00:00:00+01	0
2022-09-01 00:00:00+01	4000

SELECT 4

fiddle

Note the running_total expression should read sum(total_profit) over (order by profit_month) in ideal world, however Postgres is not aware of aliases in same select clause (even the group by <alias_from_select_clause> is Postgres' sugar and is unusual for other db vendors).

score 0 · Answer 3 · answered Mar 12 '23 at 21:40

you can check the month where you got first over 0 and use that to filter all values

Select to_char(P."date", 'YYYY-MM') as profit_month, SUM(P.profit) as total_profit
from personal_profit P

group by profit_month
having to_char(P."date", 'YYYY-MM') >= (Select  
 DISTINCT  MIN(
  to_char(P."date", 'YYYY-MM')
  ) over() as min_month
from personal_profit P
GROUP BY to_char(P."date", 'YYYY-MM')
having SUM(P.profit) > 0
)
order by profit_month;

profit_month	total_profit
2022-04	7000
2022-05	1000
2022-06	0
2022-09	4000

SELECT 4

fiddle

SQL to omit initial records until a column value is greater than zero

3 Answers3