Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  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

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.