RelationalDBDesign 




SQL Extensions  «Prev  Next»
Lesson 5 Group operators
Objective Describe group operators and what they do.

SQL Group Operators

Group functions

When you combine rows of information together and display summarized data, you are performing group functions on the data. The table below shows you the group functions that can be used in Oracle:
Group function: A group function is a predefined way to combine rows of information together and display summarized data. A common group function is the SUM function, which adds values into a total sum.

Group function

Description

SUM() Adds up a total amount for the column or expression within the parentheses
MAX() Finds the maximum value for the column or expression within the parentheses
MIN() Finds the minimum value of the column or expression within the parentheses
AVG() Finds the average value (ignoring null values)
COUNT() Finds the number of rows in which the column being counted is not null
STDDEV() Calculates the standard deviation of values
VARIANCE() Calculates the variance of values


Here is a typical query that adds up sales by customer:
SELECT FIRSTNAME, LASTNAME, SUM(TOTAL_SALE_AMOUNT)
FROM CUSTOMER C, CUSTOMER_SALE CS
WHERE C.CUST_ID = CS.CUST_ID
GROUP BY FIRSTNAME, LASTNAME
ORDER BY FIRSTNAME, LASTNAME
The GROUP BY clause determines how the SUM function groups the query rows when it adds them up. In the example, each unique combination of FIRSTNAME and LASTNAME has a summary of the TOTAL_SALES_AMOUNT column. While GROUP BY is not required, it is a common ingredient in queries that use group functions.
Whenever you use the GROUP BY clause, add a corresponding ORDER BY clause so that all rows are evaluated and grouped properly.
The next lesson describes three group operators you can use when working with groups of data.