I would create the query as you've shown for the current year, and then the same for the previous year, and join the two, like this:
select <columns, math expressions to create percentages> from ( <this year's query> ) as this_year inner join ( <last year's query> ) as last_year using (year, month); This is probably something a little like what you are trying to do, logically; but you may need a left outer join, and you may need to do something else entirely to make it work efficiently if your data is large. On Sat, Jan 3, 2009 at 8:27 PM, Eudald Lerga <eud...@digitalecologies.com> wrote: > 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 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=ba...@xaprb.com > > -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
