|
|
|
Oracle:
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:
For
example, if we want to see the sum of all
sales for a title, we can issue the
following query against the pubs 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’);
|
|
|
|
|
|
