Java Reference
In-Depth Information
Reporting functions
SQL supports a number of aggregation functions that can be used to provide
statistical or summary information about groups of data elements. The standard
aggregation functions include the following:
Sum and Count
Average and Standard Deviation
Maximum and Minimum
Note
Different SQL dialects expand on this basic set of aggregate functions.
You are advised to refer to the documentation provided by the supplier of
your particular RDBMS for details of the aggregate functions provided.
A good practical example of the use of aggregate functions is the creation of a simple
sales report. The following query creates a result set that lists states and the total cost
of goods sold and sales by state:
SELECT STATE, SUM(oi.QTY * COST) AS TOTAL,
SUM(oi.QTY * COST * 1.6) AS SALES
FROM ORDERS o, CUSTOMERS c, ORDERED_ITEMS oi,
INVENTORY i
WHERE O.CUSTOMER_NUMBER = C.CUSTOMER_NUMBER AND
O.ORDER_NUMBER = OI.ORDER_NUMBER AND
OI.ITEM_NUMBER = I.ITEM_NUMBER
GROUP BY STATE;
The resulting table looks like this:
STATE
TOTAL
SALES
NJ
20.41
32.65
NY
21.6
34.56
The last three sections have presented a brief discussion of creating databases and
tables, populating the tables with data, and retrieving the data. For the very simplest
of database operations, these capabilities may be sufficient. However, in real world
applications, more complex situations arise in two main areas:
In many practical applications, a complete operation cannot be expressed in a single SQL
statement, so a means of handling multiple interdependent statements is required.
There is frequently a need, particularly in larger installations, to provide some means of ensuring
the security of an application.
