Skip site navigation (1) Skip section navigation (2)

Re: repeatet summary querys per month over 5 years

From: Bartosz Dmytrak <bdmytrak(at)eranet(dot)pl>
To: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
Cc: sara(dot)schaerrer(at)vetsuisse(dot)unibe(dot)ch, pgsql-novice(at)postgresql(dot)org
Subject: Re: repeatet summary querys per month over 5 years
Date: 2012-03-15 22:03:51
Message-ID: CAD8_UcZ6H3hGEOiTydesQRZ+K5zaXHo5J9-rA3JU0u+a7LVkXw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,
it looks like tablefunc module (extension) could help
http://www.postgresql.org/docs/9.1/static/tablefunc.html

this could generate crosstab to summarize Your data. There are few good
examples of corsstab function which I think is a solution.

Regards,
Bartek


2012/3/15 Rory Campbell-Lange <rory(at)campbell-lange(dot)net>

> On 15/03/12, sara(dot)schaerrer(at)vetsuisse(dot)unibe(dot)ch (
> sara(dot)schaerrer(at)vetsuisse(dot)unibe(dot)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(at)campbell-lange(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

In response to

pgsql-novice by date

Next:From: MrTeethDate: 2012-03-15 22:40:22
Subject: Re: How do I create a drop down list?
Previous:From: Bartosz DmytrakDate: 2012-03-15 21:54:51
Subject: Re: How do I create a drop down list?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group