From: | <sara(dot)schaerrer(at)vetsuisse(dot)unibe(dot)ch> |
---|---|
To: | <rory(at)campbell-lange(dot)net> |
Cc: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: repeatet summary querys per month over 5 years |
Date: | 2012-03-16 08:49:23 |
Message-ID: | 983001A7B5598D4D8E8D759F33B630FE0B6FF8C9@aai-exch-mbx2.campus.unibe.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Thanks a lot for your answer, it looks promising. But I still have a problem. I'll try to give you more details in amore comprehensive way.
The sourcetable contains informations about every stay of each animal on a specific farm:
animalID animalbirthday animalsex farmID farmproductiontype beginn(Date) end(date)
to get the number of animals in different ageclasses per month, I have to calculate the age of the animals for every month (e.g. 2001-01-01 - birthday) and categorize the ageclasses and select the animals that are alive (i.e. stay on a farm) on the first for every month (begin <= 2001-01-01 and end >= 2001-01-01).
To do so the query I got looks like
SELECT
prductiontype,
animalsex,
CASE WHEN (2007-01-01)-animalbirhtday < 365 THEN '1' WHEN (2007-01-01)-animalbirhtday > 730 THEN '3' ELSE '2' END AS ageclass,
COUNT(animalID),
FROM sourcetable
WHERE begin <= 2007-01-01 and end >= 2007-01-01
GROUP BY farmproductiontype, animalsex, ageclass
ORDER BY farmproductiontype, animalsex, ageclass
wich produces me a table like
farmrpoductiontype animalsex ageclasse count
1 female 1 10000
1 male 1 10000
etc.... now I have to do this 60 times with reference date (here 2007-01-01) as the only thing changing. I'd like to automatize that and just ad each time a column to the table above
farmrpoductiontype animalsex ageclasse jan07 feb07 ....
1 female 1 10000 10003
1 male 1 10000 9000
In R I'd use a loop with for i=1:60 with begindat = 2011-07-01 and steps=month etc but I don't know if one can generate loops in postgresql with a variable in the query changing (I think not, right?)
I see the way I could use your solution assigning categories per month (e.g. CASE WHEN begin <= 2007-01-01 and end >= 2007-01-01 THEN Jan07 WHEN begin <= 2007-02-01 and end >= 2007-02-01 THEN feb07 etc) in the inner query but this would multiplie the table as the animals stay usualy longer than one month on a farm. As also the ageclasses change for every month I think it gets to complicatet...
In a book about SQL (Alan Beaulieu, Introduction to SQL in German) I found, that its possible to use subqueries to generate expressions like
SELECT
x
(SELECT y FROM tabley) y
FROM table x;
but in postgress I get the error statement that the subquery produces more than 1 row... (this would be the other obtion, create 60 subqueries to produce the final table in one step).
In the end I guess I loose less time doing a lot by hand than trying to understand the possibilities of SQL by learning by doing... but oviously if there is a solution I'm very happy to learn, especially as I have to generate other summaries over the 60 months, this is just the first one...
I hope I could explain better and again thanks a lot!
Sara
________________________________________
Von: Rory Campbell-Lange [rory(at)campbell-lange(dot)net]
Gesendet: Donnerstag, 15. März 2012 19:17
An: Schaerrer, Sara (VETSUISSE)
Cc: pgsql-novice(at)postgresql(dot)org
Betreff: Re: [NOVICE] repeatet summary querys per month over 5 years
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2012-03-16 14:29:00 | Re: PG Admin - Import from URL? |
Previous Message | Lew | 2012-03-16 06:03:49 | Re: How do I create a drop down list? |