Question 89419 in PostgreSQL Question posted in
The official documentation can be found here.

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

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
Tags:

3

Answers


  1. 0

    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).

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search