SELECT Statement (Aggregates)

Retrieve data from one or more tables, views, or snapshots.

Summary of Syntax:

   SELECT [hint][DISTINCT] group_function(column1), column2, group_function(column3)…
   FROM table_list
   [WHERE conditions]
   [GROUP BY group_by_list]
   [HAVING search_conditions]
   [ORDER BY order_list [ASC | DESC] ]

key:

group_function
One of the following Aggregate functions:

AVG(expression)   Average of the values
COUNT(expression) Count the number of rows
COUNT(*)          Count the number of rows including NULLs
MAX(expression)   Maximum value
MIN(expression)   Minimum value
SUM(expression)   Add the value for all rows in the query

group_by_list
The GROUP BY clause partitions the result set into groups.
The rows in each group having a unique value in group_by_list.
The group_by_list can be one or more columns or expressions.
Columns in the SELECT clause which are not in the GROUP BY clause must be part of an AGGREGATE function.

For example, the Order_Items table contains:

oi_shipping   oi_value   oi_units
ldn           89.75      2
ny            12.99      1
ldn           55.15      4
edi           23.00      6

To partition the result set into the three groups: ldn, ny, edi -

SELECT oi_shipping,avg(oi_value), max(oi_units)
FROM Order_Items
GROUP BY oi_shipping;

HAVING search_conditions
An additional filter - the HAVING clause acts as an additional filter to the grouped result rows - as opposed to the WHERE clause that applies to individual rows. The HAVING clause is most commonly used in conjunction with a GROUP BY clause.

In the example above you could add
HAVING avg(oi_value) <56
In the case of ldn the average will be greater than 56 so it wont be included in the results.

ORDER BY order_list [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted ascending (1…9 a…z) or descending (9…1 z…a).

More complex queries can be written by combining several SELECT commands in a single SQL statement:
SELECT command { UNION | UNION ALL | INTERSECT | MINUS } SELECT command]

“Life is like a 10 speed bicycle. Most of us have gears we never use” ~ C. Schultz

Related Oracle Commands:

Outer Joins
An online SQL Tutorial
SQL reference books

DELETE
EXPLAIN PLAN
INSERT
SELECT
Correlated SubQueries
TRUNCATE
UPDATE
DBMS_LOCK
DBMS_SQL

Related Views:

  DBA_SNAPSHOTS        ALL_SNAPSHOTS        USER_SNAPSHOTS 
  DBA_ALL_TABLES       ALL_ALL_TABLES       USER_ALL_TABLES
  DBA_TABLES           ALL_TABLES           USER_TABLES         TAB
  DBA_VIEWS            ALL_VIEWS            USER_VIEWS
                                                                DICTIONARY
                                                                DICT_COLUMNS