SQL MySQL 连接两个表从第二个表计数和求和

MySQL Join two tables count and sum from second table

提问by Ole_S

I have tow tables:

我有拖车：

dealers with some fields and primary key id

具有某些字段和主键 ID 的经销商

and inquiries with following fields id dealer_id costs

和带有以下字段的查询 id Dealer_id 成本

There are several items in inquiries for every dealer and i have to count them and sum the costs. Now I have only the count with this statement:

每个经销商都有几项要查询的项目，我必须计算它们并总结成本。现在我只有这个语句的计数：

SELECT a.*, Count(b.id) as counttotal
FROM dealers a
LEFT JOIN inquiries b on a.id=b.dealer_id
GROUP BY a.id
ORDER BY name ASC

but i have no idea how to sum the costs of table b for each dealer. Can anybody help? Thanks in advance

但我不知道如何为每个经销商计算表 b 的成本。有人可以帮忙吗？提前致谢

回答by Mike Dinescu

You could use two sub-queries:

您可以使用两个子查询：

SELECT  a.*
      , (SELECT Count(b.id) FROM inquiries I1 WHERE I1.dealer_id = a.id) as counttotal
      , (SELECT SUM(b.cost) FROM inquiries I2 WHERE I2.dealer_id = a.id) as turnover
FROM dealers a
ORDER BY name ASC

Or

或者

SELECT  a.*
     , COALESCE(T.counttotal, 0) as counttotal   -- use coalesce or equiv. to turn NULLs to 0
     , COALESCE(T.turnover, 0) as turnover       -- use coalesce or equiv. to turn NULLs to 0
 FROM dealers a
 LEFT OUTER JOIN (SELECT a.id, Count(b.id) as counttotal, SUM(b.cost) as turnover
               FROM dealers a1 
               INNER JOIN inquiries b ON a1.id = b.dealer_id
              GROUP BY a.id) T
         ON a.id = T.id
ORDER BY a.name

回答by divyabharathi

SELECT a.*, Sum(b.id) as TotalCost
FROM dealers a
LEFT JOIN inquiries b on a.id=b.dealer_id
GROUP BY a.id
ORDER BY name ASC

回答by Taryn

If I am understanding your question, all you need to do is add a SUM():

如果我理解你的问题，你需要做的就是添加一个SUM()：

SELECT a.*, 
  Count(b.id) as counttotal,
  sum(b.costs) TotalCost
FROM dealers a
LEFT JOIN inquiries b on a.id=b.dealer_id
GROUP BY a.id
ORDER BY name ASC

My suggestion would be to use a subquery to get the countand the sum:

我的建议是使用子查询来获取count和sum：

SELECT a.*, 
  b.countTotal,
  b.TotalCosts
FROM dealers a
LEFT JOIN
(
    select COUNT(ID) countTotal,
        SUM(costs) TotalCosts,
        dealer_id
    from inquiries
    group by dealer_id
) b 
    on a.id=b.dealer_id
ORDER BY name ASC

I am guessing from your original query that you are using MySQL. I would suggest using a subquery because MySQL uses an extension to GROUP BY which allows items in a select list to be nonaggregated and not included in the GROUP BYclause. This however can lead to unexpected results because MySQL can choose the values that are returned. (See MySQL Extensions to GROUP BY)

我从您的原始查询中猜测您正在使用 MySQL。我建议使用子查询，因为 MySQL 使用了对 GROUP BY 的扩展，它允许选择列表中的项目不被聚合并且不包含在GROUP BY子句中。然而，这可能会导致意外结果，因为 MySQL 可以选择返回的值。（请参阅GROUP BY 的 MySQL 扩展）

From the MySQL Docs:

来自 MySQL 文档：

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

MySQL 扩展了 GROUP BY 的使用，以便选择列表可以引用未在 GROUP BY 子句中命名的非聚合列。...您可以使用此功能通过避免不必要的列排序和分组来获得更好的性能。但是，这主要在未在 GROUP BY 中命名的每个非聚合列中的所有值对于每个组都相同时很有用。服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的。此外，添加 ORDER BY 子句不会影响从每个组中选择值。结果集的排序发生在选择值之后，并且 ORDER BY 不影响服务器选择的值。

回答by Dege

SELECT a.*, COUNT(b.id) AS counttotal, SUM(b.costs) AS total
FROM dealers AS a
LEFT JOIN inquiries AS b ON a.id=b.dealer_id
GROUP BY a.id
ORDER BY name ASC

回答by Pieter Geerkens

Just add , Sum(b.costs) as costsTotalto your select list.

只需添加, Sum(b.costs) as costsTotal到您的选择列表。

回答by Andres Weber

makeing a sub table, with the desired fields, and joining it, not the full table will make the things easyer

制作一个带有所需字段的子表，然后加入它，而不是完整的表将使事情变得更容易

SELECT a.*, b.count_total, b.costs_of_table
FROM dealers AS a
LEFT JOIN (

SELECT aux.dealer_id, Count(aux.id) AS 'count_total', Sum(aux.costs) AS 'costs_of_table'
FROM inquiries AS aux
GROUP BY dealer_id)

AS b ON a.id = b.dealer_id
WHERE (what you want)
ORDER BY name ASC

SELECT a.*, b.count_total, b.costs_of_table
FROM 经销商作为
LEFT JOIN (

SELECT aux.dealer_id, Count(aux.id) AS 'count_total', Sum(aux.costs) AS 'costs_of_table'
FROM 查询 AS aux
GROUP BY Dealer_id)

AS b ON a.id = b.dealer_id
WHERE (what you want)
ORDER BY name ASC

sorry for the bar spelling if any

对不起，如果有的话，拼写