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 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=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
