Problems with Function + SQL

[intervelopment]

Hey All,

Im having a problem with a query and function im writing.

I have 2 database tables.

The first one, lets call it scores, has the columns email, correct, time, stamp and month

the second table, which is called users, has email, dispname, country

example of the first table

| email | correct | time | stamp | month |
| [e-mail] | 3 | 2 | 12345678 | 11 |
| [e-mail] | 6 | 12 | 12345678 | 11 |
| [e-mail] | 12 | 30 | 12345678 | 11 |

| [e-mail] | 122 | 10 | 12345678 | 11 |

So what i need to do is extract all the rows with the same email address and the same month, then add the correct fields and the time fields together so i am left with the following result

| email | correct | time | stamp | month |
| [e-mail] | 9 | 4 | 12345678 | 11 |

| [e-mail] | 134 | 40 | 12345678 | 11 |

and so on for every email address that happens to be inserted into the scores table.

Once that that has been completed, i need to find the top 10 people with the highest "correct" score and with the lowest "time" score, so they are ranked according to their score and time.

Once the top 10 have been found, i then need to search the 'users' table to find the dispname and country of each of the outputted top 10 users.

and finally i need to display the top 10 as:

rank - dispname - country - correct - time

If anyone could help me out .... i would be so greatful ... its doing my head in something bad hahah

Thanks

[Roger_Ramjet]

This is a pretty straight forward GROUP BY query really

SELECT email, SUM(correct) AS c, SUM(time) AS t, dispname, country
FROM scores INNER JOIN user USING(email)
GROUP BY email, correct, time
ORDER BY c ASC, t DESC
LIMIT 10

That will give you the results you want.

Add the rank field when you display them.