title: MySQL: How to select and display ALL rows from one table, and calculate the sum of a where clause on another table?
body: <p>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.</p>
  
  <p>I need to display a report of all employees...</p>
  
  <pre><code>SELECT Employees.Name, Employees.Extension 
  FROM Employees;
  
  --------------
  | Name | Ext |
  --------------
  | Joe  | 123 |
  | Jane | 124 |
  | John | 125 |
  --------------
  </code></pre>
  
  <p>...and join some information from the PhoneCalls table...</p>
  
  <pre><code>--------------------------------------------------------------
  | 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     |
  ------------------------------------------------------------
  </code></pre>
  
  <p>Now I want to filter out some of the rows that are included in the SUM and AVG calculations...</p>
  
  <pre><code>WHERE PhoneCalls.StartTime BETWEEN "2010-09-12 09:30:00" AND NOW()
  </code></pre>
  
  <p>...which will ideally result in a table looking something like this:</p>
  
  <pre><code>------------------------------------------------------------
  | Name | Ext | CallCount | AverageCallTime | TotalCallTime |
  ------------------------------------------------------------
  | Joe  | 123 |      5    |       200       |      1000     |
  | Jane | 124 |     10    |       250       |      2500     |
  | John | 125 |      0    |         0       |         0     | 
  ------------------------------------------------------------
  </code></pre>
  
  <p>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.</p>
  
  <p>How can I create a select statement that displays all of the Employees and only SUMs/AVGs the values returned from the WHERE clause?</p>