Hello, readers! I hope you found my previous posts on Oracle and database useful. I recently learned the grouping functions in Oracle. It’s a very important feature that Oracle offers. Let us see what it is.
First of all, we look into the flow through which a query executes in Oracle. For example, we have a query below.
Query order of execution flow in Oracle
SELECT roll, name FROM student WHERE age<20;
Here, the Clause Trigger flow is as below:
Projection [FROM] ——>[WHERE] predicate——-> [SELECT] predicate.
So, first of all, Oracle learns FROM where it has to query the data, then it checks the condition as specified in the WHERE clause. After checking, it picks up the queried data using SELECT clause from the table and shows it up to the user.
Actually, there is a hidden step too. Oracle arranges the record in groups. If no grouping instruction is available in the query, then the records will be put into a single group and such query is known as ‘Single Group Query’. So, Oracle creates a group by default, which is hidden from the user.
Functions in SQL
Now we know how the queries work, let us move into the SQL functions. The SQL Functions can be divided into:
- Row-level functions
- Group-level functions
The row level functions execute each time for every row being fetched. Whereas, the group level functions execute once for each group. Look at the examples below.
Some ROW LEVEL function queries:
SELECT * FROM student WHERE UPPER(name) = ‘SHAHRUKH’; SELECT TO_CHAR(SYSDATE,’MM/YY/YY’) from DUAL; SELECT bookname, LPAD(price,5,’0’) from book;
An example of Group level function query:
SELECT SUM(price) from book;
In the above query, the FROM clause is processed firstly, so it knows, that it has to do something from the table ‘book’. Then all the rows of the ‘price’ column are pushed into a group. So, now instead of executing the SUM function each time for every row entry of the ‘price’ column, the SUM function executes only once for the group created.
There are some Aggregate functions in Oracle which group data and execute the function on it.
As the names of the above functions are pretty much self-described, I won’t discuss them all. So, I will just clarify what is Grouping using the functions above.Things to note about a Grouping function:
- They do not consider null values. That means it does nothing special if it encounters an NULL-valued record.
Example: The SUM function doesn’t consider the NULL value at all if it encounters such a record. It just executes as if that record wasn’t present.
- COUNT function is a bit different. Count returns the value ‘0’ if there is no data available to it.
Whereas, the other 4 functions can never return the value ‘0’
Finally, I will explain GROUP and HAVING clauses in the next post. I hope my articles on OpenCV Face detection were useful. So, Give this website a bookmark to make sure that you don’t miss upcoming tutorials.