Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

7.5 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, NULL, NULL, 'Grand Totals'
FROM dual
ORDER BY employee_id NULLS LAST, project_id, time_log_date;

You can practically plug this second query into Example 7-4 in place of the first. You would need to make
only two other changes. First, you would eliminate the project breaks
and computations, changing the BREAK and COLUMN commands:

BREAK ON REPORT -
ON employee_id SKIP PAGE NODUPLICATES -
ON employee_name NODUPLICATES
COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id
COMPUTE SUM LABEL 'Grand Totals' OF hours_logged ON REPORT
COMPUTE SUM LABEL 'Grand Totals' OF dollars_charged ON REPORT Then you might change the title of the date field, which represents
the most recent date an employee worked on a project, to something
more descriptive:

COLUMN time_log_date HEADING 'Last Date|Worked' FORMAT A11 Finally, you might change the title from Project Hours and Dollars
Detail to Project Hours and Dollars
Summary . Example 7-6 shows the
resulting script.

Example 7-6. A summary report

SET ECHO OFF
SET RECSEP OFF
--Set up pagesize parameters
SET NEWPAGE 1
SET PAGESIZE 55
--Set the linesize, which must match the number of equals signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 71
--Set up page headings and footings
TTITLE CENTER "The Fictional Company" SKIP 3 -
LEFT "I.S. Department" -
RIGHT "Project Hours and Dollars Detail" SKIP 1 -
LEFT "========================================" -
"===============================" -
SKIP 2 "Employee: " FORMAT 9999 emp_id_var " " emp_name_var SKIP 3
BTITLE LEFT "========================================" -
"===============================" -
SKIP 1 -
RIGHT "Page " FORMAT 999 SQL.PNO
--Format the columns
COLUMN employee_id NEW_VALUE emp_id_var NOPRINT
COLUMN employee_name NEW_VALUE emp_name_var NOPRINT
COLUMN project_id HEADING " Proj ID" FORMAT 9999
COLUMN project_name HEADING "Project Name" FORMAT A26 WORD_WRAPPED
COLUMN time_log_date HEADING "Last Date|Worked" FORMAT A11
COLUMN hours_logged HEADING "Hours" FORMAT 9,999
COLUMN dollars_charged HEADING "Dollars|Charged" FORMAT $999,999.99
--Breaks and computations
BREAK ON REPORT -
ON employee_id SKIP PAGE NODUPLICATES -
ON employee_name NODUPLICATES
COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id
SET ECHO OFF
SET RECSEP OFF
--Set up pagesize parameters
SET NEWPAGE 1
SET PAGESIZE 55
--Set the linesize, which must match the number of equals signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 71
--Set up page headings and footings
TTITLE CENTER "The Fictional Company" SKIP 3 -
LEFT "I.S. Department" -
RIGHT "Project Hours and Dollars Summary" SKIP 1 -
LEFT "========================================" -
"===============================" -
SKIP 2 "Employee: " FORMAT 9999 emp_id_var " " emp_name_var SKIP 3
BTITLE LEFT "========================================" -
"===============================" -
SKIP 1 -
RIGHT "Page " FORMAT 999 SQL.PNO
oracle@gennick02:~/sqlplus/ExampleScripts>
oracle@gennick02:~/sqlplus/ExampleScripts> cat ex7-6.sql
SET ECHO OFF
SET RECSEP OFF
--Set up pagesize parameters
SET NEWPAGE 1
SET PAGESIZE 55
--Set the linesize, which must match the number of equals signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 71
--Set up page headings and footings
TTITLE CENTER "The Fictional Company" SKIP 3 -
LEFT "I.S. Department" -
RIGHT "Project Hours and Dollars Summary" SKIP 1 -
LEFT "========================================" -
"===============================" -
SKIP 2 "Employee: " FORMAT 9999 emp_id_var " " emp_name_var SKIP 3
BTITLE LEFT "========================================" -
"===============================" -
SKIP 1 -
RIGHT "Page " FORMAT 999 SQL.PNO
--Format the columns
COLUMN employee_id NEW_VALUE emp_id_var NOPRINT
COLUMN employee_name NEW_VALUE emp_name_var NOPRINT
COLUMN project_id HEADING " Proj ID" FORMAT 9999
COLUMN project_name HEADING "Project Name" FORMAT A26 WORD_WRAPPED
COLUMN time_log_date HEADING "Last Date|Worked" FORMAT A11
COLUMN hours_logged HEADING "Hours" FORMAT 9,999
COLUMN dollars_charged HEADING "Dollars|Charged" FORMAT $999,999.99
--Breaks and computations
BREAK ON REPORT -
ON employee_id SKIP PAGE NODUPLICATES -
ON employee_name NODUPLICATES
COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id
COMPUTE SUM LABEL 'Grand Totals' OF hours_logged ON REPORT
COMPUTE SUM LABEL 'Grand Totals' OF dollars_charged ON REPORT
--Execute the query to generate the report.
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, NULL, NULL, 'Grand Totals'
FROM dual
ORDER BY employee_id NULLS LAST, project_id, time_log_date;
EXIT The resulting output would look like this:

The Fictional Company
I.S. Department Project Hours and Dollars Summary
===============================================================
Employee: 101 Marusia Churai
Last Date Dollars
Proj ID Project Name Worked Hours Charged
------------ -------------------------- ----------- ------ ------------
1001 Corporate Web Site 01-Nov-2004 20 $3,380.00
1002 Enterprise Resource 01-Dec-2004 24 $4,056.00
Planning System
...
The Fictional Company
I.S. Department Project Hours and Dollars Summary
===============================================================
Employee: Grand Totals
Last Date Dollars
Proj ID Project Name Worked Hours Charged
------------ -------------------------- ----------- ------ ------------
------ ------------
Grand Totals 786 $110,779.00 By letting the database handle the project-level summarization, you
save time and paper. You save time because SQL*Plus
doesn't need to pull all that data down from the
database, and you save paper because you don't print
all the unneeded detail.