 |
|
Oracle Concepts by Burleson
Consulting |
SQL Aggregation
Relational databases offer several ways to
aggregate and summarize data, we need to understand these mechanisms
so that we can quickly use SQL to extract data from our relational
database. Aggregation functions are categorized into two groups;
the basic aggregation functions, and the analytical aggregation
functions.
Basic Aggregation functions
While the SQL*Plus compute command can be used
to summarize data, there are times when we want to see summary
information in the result sets of our SQL output. The relational
model offers the following basic aggregation functions:
* Sum
* Avg
* Min
* Max
For example, if we want to see the sum of all
sales for a title, we can issue the following query against the sample
database:
select
book_title,
sum(quantity)
from
book
natural join
sales
group by
book_title;
Using the same query, we can substitute the
sum function for the avg function and see the average sales by author.
Note the use of the group by operator. The group by operator is
required whenever a sum of avg aggregation is specified inside any sql
query. The rule is that all column data except for the column
being summed or averaged must appear inside the group by clause.
The min and max functions are used to return
single-row result sets from the database. For example, to find
the largest sale, we could issue this SQL query:
select
max(quantity) from sales;
More commonly, the min and max functions are
used to insert rows into other tables. For example:
insert into
customer
(name, max_credit)
values
(‘Sam’, select max(credit)
from
credit_table
where name = ‘SAM’);
Analytical SQL functions
Oracle has introduced some exciting extensions
to ANSI SQL to allow us to quickly compute aggregations and rollups.
These new statements include:
* rollup
* cube
* grouping
These simple SQL operators allow us to create
easy aggregations directly inside the SQL without having to employ
SQL*Plus break and compute statements. Let’s start by examining the
ROLLUP syntax.
* Creating tabular aggregates with ROLLUP
ROLLUP enables an SQL statement to calculate
multiple levels of subtotals across a specified group of dimensions.
It also calculates a grand total. ROLLUP is a simple extension to the
GROUP BY clause, so its syntax is extremely easy to use.
SELECT
deptno,
job,
count(*),
sum(sal)
FROM
emp
GROUP BY
ROLLUP(deptno,job);
DEPTNO JOB COUNT(*)
SUM(SAL)
--------- --------- --------- ---------
10 CLERK
1 1300
10 MANAGER
1 2450
10 PRESIDENT
1 5000
10
3 8750
20 ANALYST
2 6000
20 CLERK
2 1900
20 MANAGER
1 2975
20
5 10875
* Create cross-tabular reports with CUBE
In multidimensional jargon, a “cube” is a
cross-tabulated summary of detail rows. CUBE enables a SELECT
statement to calculate subtotals for all possible combinations of a
group of dimensions. It also calculates a grand total. This is the set
of information typically needed for all cross-tabular reports, so CUBE
can calculate a cross-tabular report with a single select statement.
Note in the example below that totals are
calculated for each department, and also for each job category.
SELECT
deptno,
job,
count(*),
sum(sal)
FROM
emp
GROUP BY
CUBE(deptno,job);
DEPTNO JOB COUNT(*)
SUM(SAL)
--------- --------- --------- ---------
10 CLERK
1 1300
10
MANAGER
1 2450
10 PRESIDENT
1 5000
10
3 8750
20 ANALYST
2 6000
20 CLERK
2 1900
20 MANAGER
1 2975
20
5 10875
30 CLERK
1 950
30 MANAGER
1 2850
30 SALESMAN
4 5600
30
6 9400
ANALYST
2 6000
CLERK
4 4150
MANAGER
3 8275
PRESIDENT
1 5000
SALESMAN
4 5600
14 29025
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
