Calculating across records

[adamsca]

I have a table with 3 fields: datefield, field1, field2.

Let's say that there are 2 records: record1, record2

Record1 has values in datefield, field1

Record2 has values in datefield, field2

The dates for both records is 2009-07

I do a query:

select field1,field2 FROM table WHERE date = '2009-07'

I then want to peform some mathematical calculations on the results, like this:

$value = $field1 * $field2

echo $value;

However, since the query results are by record, it does the calculation using the value of field1 from record1 and the value of field2 on record2. Consequently, the result is '0'.

How can I do a calculation across records?

[NogDog]

It's hard to say for sure without understanding the relationship between those 2 records. (At the moment it feels like there's something that needs normalization in the database design.) What happens if there are 3 or more records that match that date: which field1 gets multiplied by which field2?