Geek Answers Handbook

MySQL: how to select and display ALL rows from one table and calculate the sum of the where clause in another table?

I am trying to display all the rows from one table, as well as the SUM / AVG results in one column, which is the result of the where clause. This 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 attach 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 lines that are included in SUM and AVG calculations ...

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

... that ideally leads to a table that looks something like this:

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

, , CallCount , . , , . WHERE, .

select, SUMs/AVGs, , WHERE?

+5
1

:

   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

, OUTER JOIN JOIN JOIN - . WHERE OUTER JOIN, , WHERE LEFT OUTER JOIN'd, , , .

+7

More articles:


All Articles