MySQL Join two tables count and sum from second ta

问题:

I have tow tables:

dealers with some fields and primary key 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

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

回答1:

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

回答2:

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

回答3:

If I am understanding your question, all you need to do is add a 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 count and the 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 BY clause. This however can lead to unexpected results because MySQL can choose the values that are returned. (See MySQL Extensions to GROUP BY)

From the MySQL Docs:

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.

回答4:

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

回答5:

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

回答6:

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

sorry for the bar spelling if any