Obtaining the sum of value in a table

[Elias Chavarria]

Hi,

I want to sum the values from one table according to a code located in another table. For example:

Table 1:

Name........Code

John..........001
Peter.........002
Mary..........003

Table 2

Code.......Tests Results

001............1
002............2
001............3
003............5
002............7

I would like to obtain as result, the sum of the tests results for each person:

Person......Total Tests Results
John................4
Peter...............9
Mary................5

How can I do this?

Thanks for your time and help

-Elias


I tried with:

SELECT a.person, sum(b.tests_results)
FROM table1 a
LEFT JOIN table2 b ON a.code = b.code


But it didn't work, i got the following message:

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause



Edited 1 time(s). Last edit at 05/07/2007 02:09PM by Elias Chavarria.