From: Baron Schwartz (baronxaprb.com)
Date: Sun Jan 11 2009 - 18:15:03 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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
<eudalddigitalecologies.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=baronxaprb.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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]