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 10 11 12 total
2005 27 36 52 42 34 28 28 40 23 31 16 8 365
2006 38 47 44 40 33 38 44 49 49 53 56 56 547
2007 75 54 72 68 59 80 71 82 75 84 6 0 726
2008 78 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 %
2005 27 36
2006 38 40.7% 47 30.5%
2007 75 97.4% 54 14.8%
2008 78 11.1% 87 61.1%
Any ideas on how to keep going?
Thanks in advance.
Eudald
