repeatet summary querys per month over 5 years - Mailing list pgsql-novice

Hi,
I'm using PostgreSQL for a very short time now in combination with Squirrel and I'm very happy as the performance is very good (I use huge Datasets). But now I have a problem:

Background: I have to summarize (count) the number of animals for the categories production type, ageclass and sex per month (1. of every month) over 5 years.

I could produce the query for one month:

SELECT
"AUFENTHALTE"."nuar",
CASE WHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") < 365 THEN '1' WHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") > 730 THEN 3 ELSE 2 END AS AK,
CASE WHEN "AUFENTHALTE"."isweiblich" = 'T' THEN 'female' ELSE 'male' END AS sex,
COUNT("AUFENTHALTE"."tierid")
FROM "TVD_db"."AUFENTHALTE"
WHERE DATE("AUFENTHALTE"."gueltigvon") <= DATE('2007-01-01')
AND DATE("AUFENTHALTE"."gueltigbis") >= DATE('2007-01-01')
GROUP BY "AUFENTHALTE"."nuar",
CASE WHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") < 365 THEN '1' WHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") > 730 THEN 3 ELSE 2 END,
CASE WHEN "AUFENTHALTE"."isweiblich" = 'T' THEN 'female' ELSE 'male' END
ORDER BY "AUFENTHALTE"."nuar",
CASE WHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") < 365 THEN '1' WHEN DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") > 730 THEN 3 ELSE 2 END,
CASE WHEN "AUFENTHALTE"."isweiblich" = 'T' THEN 'female' ELSE 'male' END;


now I should repeat this for every month from january 07 to dez 11. Ideally I'd generate a table with a column for every month:

nuar    ak    sex jan07     feb07    ...     dez11

but I failed to find a way to do it. generating a table with the named columns was no problem, also fill in the first rows, but after the insert statement (e.g for feb07) attached the values below the values generated with the query for january07. now i tried to pack the above statement in a subquery with the intention to do so for every month:

INSERT INTO "TVD_db"."lebendetiere" (nuar,ak ,sex,jan07,feb07)
SELECT
"AUFENTHALTE"."nuar",
case when DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") < 365 then '1' when DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") > 730 then 3 else 2 end as AK,
case when "AUFENTHALTE"."isweiblich" = 'T' then 'female' else 'male' end as sex,
(SELECT COUNT("AUFENTHALTE"."tierid") FROM "TVD_db"."AUFENTHALTE" where DATE("AUFENTHALTE"."gueltigvon") <= DATE('2007-01-01') and DATE("AUFENTHALTE"."gueltigbis") >= DATE('2007-01-01') GROUP BY "AUFENTHALTE"."nuar", case when DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") < 365 then '1' when DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") > 730 then 3 else 2 end, case when "AUFENTHALTE"."isweiblich" = 'T' then 'female' else 'male' end order by "AUFENTHALTE"."nuar",
case when DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") < 365 then '1' when DATE ('2007-01-01')- DATE ("AUFENTHALTE"."gebdat") > 730 then 3 else 2 end,
case when "AUFENTHALTE"."isweiblich" = 'T' then 'female' else 'male' end) as jan07,
(SELECT COUNT("AUFENTHALTE"."tierid") FROM "TVD_db"."AUFENTHALTE" where DATE("AUFENTHALTE"."gueltigvon") <= DATE('2007-02-01') and DATE("AUFENTHALTE"."gueltigbis") >= DATE('2007-02-01') GROUP BY "AUFENTHALTE"."nuar", case when DATE ('2007-02-01')- DATE ("AUFENTHALTE"."gebdat") < 365 then '1' when DATE ('2007-02-01')- DATE ("AUFENTHALTE"."gebdat") > 730 then 3 else 2 end, case when "AUFENTHALTE"."isweiblich" = 'T' then 'female' else 'male' end order by "AUFENTHALTE"."nuar",
case when DATE ('2007-02-01')- DATE ("AUFENTHALTE"."gebdat") < 365 then '1' when DATE ('2007-02-01')- DATE ("AUFENTHALTE"."gebdat") > 730 then 3 else 2 end,
case when "AUFENTHALTE"."isweiblich" = 'T' then 'female' else 'male' end) as feb07
FROM "TVD_db"."AUFENTHALTE";

but it gives me an error: subquery generates more than one line... I want it to generate more than one line but I don't know how to write the code so PostgreSQL accepts it.

Obviously it would be nicer to generate a loop, but I couldn't find out how the syntax works and how to set a date to beginn and ask him to move in steps of 1 month... is that even possible?

If there is no easy solution I'll put the results/month together by hand, but it would be nice to save me some work...

Thank you very much in advance for your answer!

Sincerely
Sara