Dynamic column names in view (Postgres)

My first inclination is to produce this table:

+---------+-------+--------+ | Country | Month | Amount | +---------+-------+--------+ | UK | Jan | 4 | +---------+-------+--------+ | UK | Feb | 12 | +---------+-------+--------+ 

etc. and pivot it. So you'd start with (for example):

SELECT c.country, EXTRACT(MONTH FROM s.eldate) AS month, COUNT(*) AS amount FROM country AS c JOIN site AS s ON s.country_id = c.id WHERE s.eldate > NOW() - INTERVAL '1 year' GROUP BY c.country, EXTRACT(MONTH FROM s.eldate); 

You could then plug that into one the crosstab functions from the tablefunc module to achieve the pivot, doing something like this:

SELECT * FROM crosstab('<query from above goes here>') AS ct(country varchar, january integer, february integer, ... december integer); 

You could truncate the dates to make the comparable:

WHERE date_trunc('month', eldate) = date_trunc('month', now()) - interval '12 months' 

UPDATE

This kind of replacement for your query:

(SELECT COUNT("C1".eldate) FROM "C1" WHERE date_trunc('month', "C1".eldate) = date_trunc('month', now()) - interval '12 months') AS TWELVE_MONTHS_AGO 

But that would involve a scan of the table for each month, so you could do a single scan with something more along these lines:

SELECT SUM( CASE WHEN date_trunc('month', "C1".eldate) = date_trunc('month', now()) - interval '12 months' THEN 1 ELSE 0 END ) AS TWELVE_MONTHS_AGO ,SUM( CASE WHEN date_trunc('month', "C1".eldate) = date_trunc('month', now()) - interval '11 months' THEN 1 ELSE 0 END ) AS ELEVEN_MONTHS_AGO ... 

or do a join with a table of months as others are showing.

UPDATE2

Further to the comment on fixing the columns from Jan to Dec, I was thinking something like this: filter on the last years worth of records, then sum on the appropriate month. Perhaps like this:

SELECT SUM( CASE WHEN EXTRACT(MONTH FROM "C1".eldate) = 1 THEN 1 ELSE 0 END ) AS JAN ,SUM( CASE WHEN EXTRACT(MONTH FROM "C1".eldate) = 2 THEN 1 ELSE 0 END ) AS FEB ... WHERE date_trunc('month', "C1".eldate) < date_trunc('month', now()) AND date_trunc('month', "C1".eldate) >= date_trunc('month', now()) - interval '12 months'