CODEWITHSUNDEEP
sqlpostgresqlloopsfor-loopparameters
Ale J. Caua
Ale J. Caua

Reputation: 21

PostgreSQL - how to loop over a parameter to make union of different queries of the same table

I need to count the number of events still occurring in a given year, provided these events started before the given year and ended after it. So, the query I'm using now is just a pile of unparametrized queries and I'd like to write it out with a loop on a parameter:

select
  count("starting_date" ) as "number_of_events" ,'2020' "year"
  from public.table_of_events
  where "starting_date" < '2020-01-01' and  ("closing_date" > '2020-12-31')
union all
select
  count("starting_date" ) as "number_of_events" ,'2019' "year"
  from public.table_of_events
  where "starting_date" < '2019-01-01' and  ("closing_date" > '2019-12-31')
union all
select
  count("starting_date" ) as "number_of_events" ,'2018' "year"
  from public.table_of_events
  where "starting_date" < '2018-01-01' and  ("closing_date" > '2018-12-31')
...
...
...
and so on for N years

So, I have ONE table that must be filtered according to one parameter that is both part of the select statement and the where clause

The general aspect of the "atomic" query is then

select
  count("starting_date" ) as "number_of_events" , **PARAMETER** "year"
  from public.table_of_events
  where "starting_date" < **PARAMETER**  and  ("closing_date" > **PARAMETER** )
union all

Can anyone help me put this in a more formal loop?

Thanks a lot, I hope I was clear enough.

Upvotes: 0

Views: 642

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You seem to want events that span entire years. Perhaps a simple way is to generate the years, then use join and aggregate:

select gs.yyyy, count(e.starting_date)
from public.table_of_events e left join
     generate_series('2015-01-01'::date,
                     '2021-01-01'::date,
                     interval '1 year'
                    ) as gs(yyyy)
     on e.starting_date < gs.yyyy and
        e.closing_date >= gs.yyyy + interval '1 year'
group by gs.yyyy;

Upvotes: 1

Related Questions