select sum()...

[shimi]

Hi,I would like to know if you can use sum(), and combine more the one table.For example, i got two tables, one table contains people names and addresses(id,fname,lname...) , and the other table contains, those people transaction history(id, name_id,date_,amount).The result, i want to get is a table with the peoples names, and the sum or their transaction, with one select.i tried something like this:

select names.id,names.fname,trns.id,sum(amount) where names.id=trns.id group by trns.id

I know this code is wrong, but i still would like to know if it is possible.thanks.shimi.

[Jack McKalling]

There are three different joining methods :)The inner JOIN, and two outer JOINs.

SELECT columnName FROM table1,table2 WHERE table1.col2 = table2.col2

SELECT columnNamesList FROM table1 LEFT JOIN table2 ON table1.col2 = table2.col2SELECT columnNamesList FROM table1 RIGHT JOIN table2 ON table1.col2 = table2.col2

Documentation: http://dev.mysql.com/doc/refman/5.0/en/multiple-tables.html

[shimi]

i know about the inner join, but what i mean is to have a field that contains a subtotal.i.eName Fname trnsa b 1a b 2a b 4a b -1(null null sum=6)c d 1c d 2c d 1c d -1(null null sum=3)the result will be:Fname Lname suma b 6c d 3possible?

[dooberry]

If you only want the totals, then try this (based on your example above)

SELECT Fname, Lname, sum(trns) as totalFROM tableGROUP BY Fname, Lname

This will also work across a valid join between two tables.