Help with a complex php mysql query

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

2 

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)

3

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

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

5

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 a.date or b.date; because you’re JOINing on the date it doesn’t really matter which one you pick, both work.

6

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