Re: repeatet summary querys per month over 5 years

On 15/03/12, sara.schaerrer@vetsuisse.unibe.ch (sara.schaerrer@vetsuisse.unibe.ch) wrote:
> 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.

...

> Ideally I'd generate a table with a column for every month:
>
> nuar    ak    sex jan07     feb07    ...     dez11

You may need a query with an outer and inner part. The inner part is a
query that provides you with the base data which is summarised in the
outer part.

I couldn't read your query but if something like this worked:

   SELECT
       animal
       ,sex
       ,tochar(dater,'YYDD') as month
   FROM
       sourcetable;

you can nest the query to get the sort of result you seem to need, as
follows:

  SELECT
      x.animal
      ,x.sex
      ,sum(case when x.month='012001' then 1 else 0 end) as '012001'
      ,sum(case when x.month='022001' then 1 else 0 end) as '022001'
      ,sum(case when x.month='032001' then 1 else 0 end) as '032001'
      ...
  FROM (
       SELECT
           animal
           ,sex
           ,tochar(dater,'MMYY') as month
       FROM
           sourcetable;
       ) x
  GROUP BY
      x.animal
      ,x.sex;

--
Rory Campbell-Lange
rory@campbell-lange.net

Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice