Re: crosstab with percent change between years

2009-01-23 Thread Eudald Lerga 

Hi Baron,

Thanks for your message.

After endless nights trying I could not get it to work, so I finally  
created a temporary table and run a second query to get the percentages.


It works fine for now, but I wonder if it will take too long once  
there are thousands of records. Is there an alternative way I should  
look into?


Thanks for your help!

Eudald


On Jan 11, 2009, at 7:15 PM, Baron Schwartz wrote:


eud...@digitalecologies.com

crosstab with percent change between years

2009-01-03 Thread Eudald Lerga 

Hi guys:

I am quite new in SQL and I need to build a crosstab based in two  
tables using Mysql, but it is becoming quite dificult.


So far I got a crosstab with the ocurrences for all months in every  
year working:


select YEAR(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y')) as Year,
SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=1) AS '1',
SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=2) AS '2',
SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=3) AS '3',
SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=4) AS '4',
SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=5) AS '5',
SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=6) AS '6',
SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=7) AS '7',
SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=8) AS '8',
SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=9) AS '9',
SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=10) AS '10',
SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=11) AS '11',
SUM(MONTH(STR_TO_DATE(F.pubnotdate, '%m/%d/%Y'))=12) AS '12',
count(*) AS total
FROM Forc as F
inner join Parc P on F.OBJECTID=P.OBJECTID
where F.rectype like 'LP%'
group by Year

this is the result:

 Year   	  1   	  2   	  3   	  4   	  5   	  6   	  7   	  8   	   
9   	1011   12   total

200527  36  52  42  34  28  28  40  23  
31  16  8   365
200638  47  44  40  33  38  44  49  49  
53  56  56  547
200775  54  72  68  59  80  71  82  75  
84  6   0   726
200878  87  90  74  76  74  65  70  78  
85  32  0   809


What I have been unsuccesfully trying to accomplish is to have a  
column next to each month with the percent change respect the previous  
year. (lastYear-previousYear)*100/previousYear .


It should look like this:

Year  1   %  2%
200527  36  
200638  40.7%   47   30.5%
200775  97.4%   54  14.8%
200878  11.1%   87  61.1%

Any ideas on how to keep going?

Thanks in advance.

Eudald

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org