MySQL: How to select and display ALL rows from one table, and calculate the sum of a where clause on another table?

Question

I'm trying to display all rows from one table and also SUM/AVG the results in one column, which is the result of a where clause. That probably doesn't make much sense, so let me explain.

I need to display a report of all employees...

SELECT Employees.Name, Employees.Extension 
FROM Employees;

--------------
| Name | Ext |
--------------
| Joe  | 123 |
| Jane | 124 |
| John | 125 |
--------------

...and join some information from the PhoneCalls table...

--------------------------------------------------------------
| PhoneCalls Table                                           |
--------------------------------------------------------------
| Ext |      StartTime      |       EndTime       | Duration |
--------------------------------------------------------------
| 123 | 2010-09-05 10:54:22 | 2010-09-05 10:58:22 |   240    |
--------------------------------------------------------------

SELECT Employees.Name, 
       Employees.Extension,
       Count(PhoneCalls.*) AS CallCount, 
       AVG(PhoneCalls.Duration) AS AverageCallTime, 
       SUM(PhoneCalls.Duration) AS TotalCallTime
FROM Employees
LEFT JOIN PhoneCalls ON Employees.Extension = PhoneCalls.Extension
GROUP BY Employees.Extension;

------------------------------------------------------------
| Name | Ext | CallCount | AverageCallTime | TotalCallTime |
------------------------------------------------------------
| Joe  | 123 |     10    |       200       |      2000     |
| Jane | 124 |     20    |       250       |      5000     |
| John | 125 |      3    |       100       |       300     |
------------------------------------------------------------

Now I want to filter out some of the rows that are included in the SUM and AVG calculations...

WHERE PhoneCalls.StartTime BETWEEN "2010-09-12 09:30:00" AND NOW()

...which will ideally result in a table looking something like this:

------------------------------------------------------------
| Name | Ext | CallCount | AverageCallTime | TotalCallTime |
------------------------------------------------------------
| Joe  | 123 |      5    |       200       |      1000     |
| Jane | 124 |     10    |       250       |      2500     |
| John | 125 |      0    |         0       |         0     | 
------------------------------------------------------------

Note that John has not made any calls in this date range, so his total CallCount is zero, but he is still in the list of results. I can't seem to figure out how to keep records like John's in the list. When I add the WHERE clause, those records are filtered out.

How can I create a select statement that displays all of the Employees and only SUMs/AVGs the values returned from the WHERE clause?

Answer 1

Use:

   SELECT e.Name, 
          e.Extension,
          Count(pc.*) AS CallCount, 
          AVG(pc.Duration) AS AverageCallTime, 
          SUM(pc.Duration) AS TotalCallTime
     FROM Employees e
LEFT JOIN PhoneCalls pc ON pc.extension = e.extension
                       AND pc.StartTime BETWEEN "2010-09-12 09:30:00" AND NOW()
 GROUP BY e.Name, e.Extension

The issue is when using an OUTER JOIN, specifying criteria in the JOIN section is applied before the JOIN takes place--like a derived table or inline view. The WHERE clause is applied after the OUTER JOIN, which is why when you specified the WHERE clause on the table being LEFT OUTER JOIN'd to that the rows you still wanted to see are being filtered out.