 |
|
Oracle Tips by Burleson |
Multi Row SQL Functions
They operate on a set of rows and returns one
result or one result per group. We will cover groups in Chapter 3.
This is a powerful feature because it allows you to generate
subtotals, sums and averages within the SQL that is retrieving the
data. For now, we will apply the functions to all the rows we
return. In the next chapter, we will break up our returning rows into
groups and apply the functions to each of the groups independently.
count
The count
function counts the number of rows
it processes and returns that number. You can use the distinct clause
to count only distinct rows.
SELECT
COUNT(*),
COUNT(1),
COUNT(store_key),
COUNT(DISTINCT store_key)
FROM
sales;
COUNT(*) COUNT(1) COUNT(STORE_KEY)
COUNT(DISTINCTSTORE_KEY)
---------- ---------- ---------------- ------------------------
100 100 100 10
First, we count the number of rows using
count(*). In the second example, we do the same thing. Some Remote DBAs
believe that count(1) is more efficient than count(*), but this is a
myth. In example three, we count the number of store_keys. If a row
is processed with a NULL store_key, it will be counted in example one
and two but not in three or four. In example four, we count distinct
store_keys. So there are 100 rows in the sales table, each row has a
store_key (no NULL store_keys) and there are ten distinct store_keys
in the table (listed below).
SQL> SELECT
DISTINCT store_key FROM sales;
STOR
----
S101
S102
S103
S104
S105
S106
S107
S108
S109
S110
sum(c1)
The function sum
adds the value of the column c1
for all the rows processed and returns the total. NULLs are skipped.
Sum can also use the distinct format.
SELECT
SUM(quantity)
FROM
sales;
SUM(QUANTITY)
-------------
110550
SELECT
SUM(quantity)
FROM
sales
WHERE
book_key = 'B104';
SUM(QUANTITY)
-------------
4000
avg(c1)
The avg
function returns the average of
the column you specify. Avg can also use the distinct format. NULLS
are skipped, not included as a zero.
SELECT
AVG(quantity)
FROM
sales;
AVG(QUANTITY)
-------------
1105.5
Below, we get the average of the distinct
quantity values in the sales table.
SELECT
AVG(DISTINCT quantity)
FROM
sales;
AVG(DISTINCTQUANTITY)
---------------------
2658.46154
min(c1)/max(c1)
The min
and max functions
returns the smallest and largest values of the column c1. NULLs are
skipped. There is no distinct format.
SELECT
MAX(quantity),
MIN(quantity)
FROM
sales
WHERE
book_key = 'B105';
MAX(QUANTITY)
MIN(QUANTITY)
------------- -------------
700 100
There are other Multi Row Functions
such as stdev and variance
that are normally used in PL/SQL.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
