MySQL divide two series by eachother

Question

I have several data series in a MySQL table.

The fields are Series, Date, Value. Is there a way for me to select, for two distinct Series, all the dates for which the series overlap and then for each date, the quotient of the value from series one and series two.

Example:

Series Date Value
Dogs 2004 10
Dogs 2005 12
Dogs 2006 14
Dogs 2007 16
Cats 2004 6
Cats 2005 6
Cats 2006 7
Cats 2007 4

I would like something like SELECT Cats/Dogs and get the following result

Date Value
2004 .6
2005 .5
2006 .5
2007 .25

Answer 1

You can achieve this with a self-join on your table.

SELECT cats.Series, cats.value / dogs.value 
FROM animals cats
INNER JOIN animals dogs 
        ON dogs.Date = cats.Date
WHERE cats.Series = "Cats"
AND dogs.Series = "Dogs"

It would be preferable for performance to have an index on Date, and a key on (Series,Date) would be ideal.