SQL内部加入Group By

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.

我已经通过使用2个sql执行实现了这一点。感谢昨晚在这个网站上提供帮助的人,我只使用了1个sql语句就可以使用它。但是,我无法将第一个表链接到第二个表以获取其他信息。

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.

我目前正在使用这个sql语句,它会弹出一个错误。

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."

它说不能“你试图执行一个不包含指定表达式'Field3'的查询作为聚合函数的一部分。”

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

如果有人知道解决这个问题,我会非常感激。

5 个解决方案

#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.

您在SELECT语句中选择的不是聚合函数的列(即,不使用COUNT,SUM或其他聚合函数的列)也应在GROUP BY子句中提及。

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.

在MySQL中并没有特别遵循这一点(有时会产生不必要的结果)但在Oracle中它是强制性的。但是,当您使用上述查询时,大多数RDBMS将产生类似的结果。

#2

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

正如Blorgbear和Matt Welch所说,您需要在组中包含不在聚合函数中的所有列。

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)

考虑一下你在其他两列中的数据,如果你没有指定对它的操作,如Min(拉出最小值),它如何知道显示100,250,或者Field2的任何其他值?

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

以下是聚合函数列表:http://www.w3schools.com/sql/sql_functions.asp

#3

You need to group by the column you included in the select:

您需要按选择中包含的列进行分组:

Select SUM(CTotal) as TransactionTotal, Table2.Field3 
from Table1 
INNER JOIN Table2 ON Table1.Field1=Table2.Field1 
GROUP BY Table1.Field3

#4

Add Field3 to the GROUP BY clause like this:

将Field3添加到GROUP BY子句中,如下所示:

Select SUM(CTotal) as TransactionTotal, Table2.Field3 from Table1 
INNER JOIN Table2 ON Table1.Field1=Table2.Field1 GROUP BY Table2.Field3

#5

Try this

Select SUM(CTotal) as TransactionTotal, max(Table2.Field3) Field3  from Table1 
INNER JOIN Table2 ON Table1.Field1=Table2.Field1 GROUP BY Table1.Field1

Select SUM(CTotal) as TransactionTotal, Table2.Field3  from Table1 
INNER JOIN Table2 ON Table1.Field1=Table2.Field1 GROUP BY Table1.Field1, Table2.Field3

#1