Help with a complex php mysql query...

[skyline]

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]

SELECT
   AVG(a.VALUE / b.VALUE)
FROM
   mytable a
   INNER JOIN
   mytable b
    ON t1.DATE=t2.DATE
   AND a.name = 'a'
   AND b.name = 'b'
WHERE
   MONTH(DATE) = 1
   AND
   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]

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

[skyline]

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]

What is the the t1 and t2? Should that not be a.DATE and b.DATE?

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

Also, the MONTH(DATE) etc, should that specified as MONTH(a.DATE)?

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]

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