Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Summary Reports

Sometimes you are interested only in summarized information. Maybe you only need to know the total hours each employee has spent on each project, and you don't care about the detail of each day's charges. Whenever that's the case, you should write your SQL query to return summarized data from Oracle.

Here is the query used in the master/detail report shown in Example 7-4:

SELECT p.project_id,
       p.project_name,
       TO_CHAR(ph.time_log_date,'dd-Mon-yyyy') time_log_date,
       ph.hours_logged,
       ph.dollars_charged,
       e.employee_id,
       e.employee_name
  FROM employee e INNER JOIN project_hours ph
       ON e.employee_id = ph.employee_id
       INNER JOIN project p
       ON p.project_id = ph.project_id
UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, 'Grand Totals'
FROM dual
ORDER BY employee_id NULLS LAST, project_id, time_log_date;

This query brings down all the detail information from the project_hours table, and is fine if you need that level of detail. However, if all you are interested in are the totals by employee and project, you can use the following query instead:

SELECT p.project_id, p.project_name, TO_CHAR(MAX(ph.time_log_date),'dd-Mon-yyyy') time_log_date, SUM(ph.hours_logged) hours_logged, SUM(ph.dollars_charged) dollars_charged, e.employee_id, e.employee_name FROM employee e INNER JOIN project_hours ph ON e.employee_id = ph.employee_id INNER JOIN project p ON p.project_id = ph.project_id GROUP BY e.employee_id, e.employee_name, p.project_id, p.project_name UNION ALL SELECT NULL, NULL, NULL, NULL, ...