Lesson 5	Group operators
Objective	Describe group operators and what they do.

Group Operators in Oracle: Function and Purpose

Group operators in Oracle play a critical role in manipulating and managing data. They allow you to perform operations on a dataset as a whole, returning a single result that summarizes the information contained within. Understanding the function and purpose of these operators is essential for effective database querying and management in Oracle Database.

Group Operators Defined

In Oracle, group operators refer to SQL operations that aggregate data from multiple rows into a single output row. This is particularly useful for performing calculations and comparisons across a dataset, enabling efficient data analysis and manipulation.

Key Group Operators in Oracle

1.GROUP BY:

Function: The `GROUP BY` clause groups rows that have the same values in specified columns into summary rows.
Purpose: It is used for aggregating data according to certain criteria, allowing for operations such as counting, summing, averaging, or finding the minimum or maximum values within each group.

2.HAVING:

Function: The `HAVING` clause sets conditions for the `GROUP BY` clause, similar to the way in which the `WHERE` clause sets conditions for the `SELECT` clause.
Purpose: It is used to filter the results of a `GROUP BY` query, excluding groups that do not meet the specified conditions.

Utilizing Group Operators: Examples

Example 1: Using `GROUP BY` Consider you want to find the total quantity of each product sold:

SELECT product_id, SUM(quantity)
FROM sales
GROUP BY product_id;

This query groups the sales table by `product_id`, and for each group, it sums the `quantity`.
Example 2: Using `GROUP BY` with `HAVING`
Assume you want to find products that have sold more than 100 units in total:

SELECT product_id, SUM(quantity)
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > 100;

This query first groups the sales table by `product_id`. It then filters these groups, only selecting those where the total quantity sold is greater than 100.

Group operators in Oracle SQL provide vital functionality for data aggregation and manipulation. Mastery of operators like `GROUP BY` and `HAVING` is crucial for generating summary statistics, conducting analyses, and extracting meaningful insights from your database. Proper utilization of these operators enhances database query efficiency, precision, and effectiveness, promoting optimal data management and utilization in Oracle Database environments.

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	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 uniquecombination 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. ^[1]

Whenever you use the GROUP BY clause, add a corresponding ORDER BY clause so that all rows are evaluated and grouped properly.

DISTINCT in Group Functions

All group-value functions have a DISTINCT versus ALL option. COUNT provides a good example of how this works. Here is the format for COUNT (note that | means or):

COUNT([DISTINCT | ALL] value)

Here is an example:

select COUNT(DISTINCT City), COUNT(City), COUNT(*)
from COMFORT;
COUNT(DISTINCTCITY) COUNT(CITY) COUNT(*)
------------------- ----------- --------
2                       8           8

This query shows a couple of interesting results. First, DISTINCT forces COUNT to count only the number of unique city names. If asked to count the DISTINCT midnight temperatures, it would return 7, because two of the eight temperatures were the same.
When COUNT is used on City but not forced to look at DISTINCT cities, it finds 8. This also shows that COUNT can work on a character column. It is not making a computation on the values in the column, as SUM or AVG must; it is merely counting how many rows have a value in the specified column. COUNT has another unique property: value can be an asterisk, meaning that COUNT tells you how many rows are in the table, regardless of whether any specific columns are NULL. It will count a row even if all its fields are NULL.
The other group functions do not share COUNT's ability to use an asterisk, nor its ability to use a character column for value (although MAX and MIN can). They do all share its use of DISTINCT, which forces each of them to operate only on unique values.
The next lesson describes three group operators you can use when working with groups of data.

[1] 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.