data table has the following columns:
id INTEGER, name TEXT, created TIMESTAMP, deleted TIMESTAMP
I want to product a report of the count of each
name (which can appear several times in the table) that is active each year.
deleted timestamp may be null, if it's currently still active).
So far I’ve managed to do this by manually entering the years in a long list of union statements (see below). I’m sure there’s a better way! And I have many more similar queries to perform. I tried to create a PL/pgSQL function, but couldn’t figure out how to get the year as a variable and also how to get the correct output. I would be very happy with a statement, or PL/pgSQL function to achieve this.
((select '2016' yr, name, count(*) from data where (((deleted - '2016-01-01'::timestamp) > '0 secs') or (deleted is null)) and (created - '2016-01-01'::timestamp) <= '0 secs' group by name order by count desc) union all ((select '2015' yr, name, count(*) from data where (((deleted - '2015-01-01'::timestamp) > '0 secs') or (deleted is null)) and (created - '2015-01-01'::timestamp) <= '0 secs' group by name order by count desc) etc..
I got the years using:
select distinct date_part('year',created) from data order by date_part('year',created);
And then manually entered them in the very long union statement. (In my case 2007-2016!)