SQL Inner Join with Group By

Question

I have the tables shown below.

Table1
Field1 Field2
ID111    1,500
ID112    100
ID111    250
ID114    50
ID114    20

Table2
Field1 Field3
ID111  Chris
ID112  Mary
ID114  John

What I'd like to have is the result shown below.

ID111 Chris 1,750
ID112 Mary  100
ID114 John  70

I've already achieved this by using 2 sql executions. And thanks to the ones who helped last night on this site, I just got it to work using only 1 sql statement. However, I'm not able to link the first table to the second table for additional information.

By using

SELECT SUM(ctotal) AS TransactionTotal 
FROM   table1 
GROUP  BY field1 

I was able to achieve

ID111 1,750
ID112 100
ID114 70

I'm currently using this sql statement and it pops out an error.

SELECT SUM(ctotal) AS TransactionTotal, 
       table2.field3 
FROM   table1 
       INNER JOIN table2 
               ON table1.field1 = table2.field1 
GROUP  BY table1.field1 

It says can't "You tried to execute a query that does not include the specified expression 'Field3' as part of an aggregate function."

If anybody knows a fix for this, I'd appreciate it very much.

Answer 1

This should suffice-

  SELECT t1.firld1, t2.field3,
         sum(t1.CTotal) as cTotal
    FROM table1 t1 INNER JOIN table2 t2
         ON t1.field1 = t2.field1
GROUP BY t1.field1, t2.field3

Whatever columns you are selecting in your SELECT statement that are not an aggregate function (i.e., the columns that are not using COUNT, SUM or other aggregate functions) should also be mentioned in the GROUP BY clause.

This is not particularly followed in MySQL (yielding unwanted results sometimes) but it is mandatory in Oracle. Yet, most of the RDBMS will yield similar result when you use the query as above.

Answer 2

As Blorgbear and Matt Welch said, you need to include all columns that are not in an aggregate function in the group by.

The reason for this is that you are trying to tell it to sum one value, but not what to put in the other columns.

Think about the data you have in the other two columns, how does it know to show 100,250, or any other value for Field2 if you don't specify an action on it such as Min (pull the minimum value)

Here is a list of aggregate functions: http://www.w3schools.com/sql/sql_functions.asp