Lesson 5 | Group operators |

Objective | Describe group operators and what they do. |

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.

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.

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`.

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.

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 *group functions.* ^{[1]}

`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
Whenever you use the GROUP BY clause, add a corresponding ORDER BY clause so that all rows are evaluated and grouped properly.

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.