skyline — 2012-05-26T17:43:18-04:00 — #1
I have a mysql table eg:
date name value
01/01/12 a 1.12345
01/01/12 b 1.7893
02/01/12 a 1.13456
02/01/12 b 1.6789
I normally have to calculate to get a derived value for a certain date eg (1 * b.value/a.value)
I am trying to get the average value AVG(value) of derived values for the entire month eg jan 2012.
How can I put this into a single query within my php script?
scallioxtx — 2012-05-26T18:02:15-04:00 — #2
AVG(a.value / b.value)
AND a.name = 'a'
AND b.name = 'b'
month(date) = 1
year(date) = 2012
(doesn't work if the date column isn't of type date, which given from your sample data I'm not sure it does)
skyline — 2012-05-26T18:28:03-04:00 — #3
Thanks! It is indeed a date column type
So how do I now get that value from that query?
skyline — 2012-05-26T18:37:03-04:00 — #4
Also I'm unclear on 2 things:
t1. DATE and t2.DATE
What is the the t1 and t2? Should that not be a.DATE and b.DATE?
Also, the MONTH(DATE) etc, should that specified as MONTH(a.DATE)?
scallioxtx — 2012-05-26T18:51:33-04:00 — #5
Ehm, yes. I changed my mind about the alias of that table half way through the query and forgot to amend it
Indeed, those should be a and b
Either a.date or b.date; because you're JOINing on the date it doesn't really matter which one you pick, both work.
skyline — 2012-05-26T18:58:34-04:00 — #6
Cool, had just figured it out as you posted All sorted ta!