 # Oracle Group Functions

This SQL tutorial focuses on the Oracle Group Functions, and provides explanations, examples and exercises. For this lesson’s exercises use this link.

This tutorial is a part of several posts explaining how to use the Oracle Group Functions. To read additional posts regarding this subject, please use the following links:

## Oracle Group Functions

Unlike Oracle Scalar Functions, Oracle Group Functions process the values of multiple rows to give one result per group, for example :

## Oracle Common Group Functions

 Function Description Syntax SUM Returns the total sum ```SELECT SUM(unit_price) FROM products -- Result: 200 ``` MIN Returns the lowest value ```SELECT MIN (unit_price) FROM products -- Result: 20 ``` MAX Returns the highest value ```SELECT MAX(unit_price) FROM products -- Result: 70 ``` AVG Returns the average value ```SELECT AVG(unit_price) FROM products -- Result: 40 ``` COUNT (*) Returns the number of records in a table ```SELECT COUNT(*) FROM products -- Result: 5 ``` COUNT (column) Returns the number of values (NULL values will not be counted) of the specified column ```SELECT COUNT(product_name) FROM products -- Result: 4 ``` COUNT (DISTINCT column) Returns the number of distinct values ```SELECT COUNT(DISTINCT category_id) FROM products -- Result :2 ```

* Results based on the illustration mentioned above

## Oracle GROUP Functions and NULL

• In Oracle, All Group functions ignore NULL values. For example: the average price is calculated based on the rows in the table where a valid value is stored (the total price divided by the number of products with a price).
• In Oracle You can use the NVL function to force group functions to include NULL values, in the following example the average is calculated based on all rows in the table, regardless of whether null values are stored in the price column (the total price divided by the total number of rows in the table):
```SELECT       AVG(NVL(unit_price,0))
FROM         products
```

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…