MySQL加入两个表计数并从第二个表求和 [英] MySQL Join two tables count and sum from second table

问题描述

我有两张桌子:

经销商带有一些字段和主键 id

dealers with some fields and primary key id

和查询具有以下字段ID经销商 ID费用

and inquiries with following fields id dealer_id costs

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

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

但我不知道如何为每个经销商汇总表 b 的成本.有人可以帮忙吗?提前致谢

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

推荐答案

你可以使用两个子查询:

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

或者

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

这篇关于MySQL加入两个表计数并从第二个表求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！