BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

   
  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

   

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.