Help with a complex php mysql query

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?

   AVG(a.value / b.value)
   mytable a
   mytable b
   AND = 'a'
   AND = '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)

Thanks! It is indeed a date column type :slight_smile:
So how do I now get that value from that query?

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)?

Ehm, yes. I changed my mind about the alias of that table half way through the query and forgot to amend it :blush:
Indeed, those should be a and b :slight_smile:

Either or; because you’re JOINing on the date it doesn’t really matter which one you pick, both work.

Cool, had just figured it out as you posted :slight_smile: All sorted ta!