Group functions Introduction

Khaled Alkhudari
A free video tutorial from Khaled Alkhudari
Sr Oracle Developer/ Sr system analyst
4.5 instructor rating • 7 courses • 24,668 students

Learn more from the full course

Oracle Database 12c SQL Certified Associate 1Z0-071

Become Oracle SQL Certified ( OCA ) / Covers ORACLE university track 100%. All the presentations & scripts are attached

19:53:19 of on-demand video • Updated January 2020

  • The Full track of Oracle Database 12c: Introduction to SQL Ed 2.
  • You will be prepared 100% to pass exams Oracle Database SQL 1Z0-071 And also exam Oracle Database 12c: SQL Fundamentals 1Z0-061
  • Very strong introduction to make you understand the oracle database and RDBMS.
  • Oracle Database 12c Architecture
  • Downloading and installing oracle database 12c in details
  • Connecting to the Database
  • Retrieving data using the select statement / Restricting and Sorting Data
  • Substitution Variables
  • Using Single-Row Functions to Customize Output
  • Reporting Aggregated Data Using the Group Functions
  • Displaying Data from multiple tables using joins
  • Using Subqueries to Solve Queries
  • Using the Set Operators
  • Managing tables using DML Statments ( Insert, Update, Delete )
  • Data Definition Language / Creating tables
  • Data Dictionary Views
  • Creating sequences, synonyms, and indexes
  • Creating Views in details
  • Managing Schema Objects
  • Retrieving Data By Using Subqueries
  • Manipulating Data By Using Subqueries
  • Controlling User Access in details
  • Manipulating Data
  • Managing Data in Different Time Zones
  • Exam information / Exams Details / Exam Practice
  • many other information, you can see the full curriculum
English [Auto] Hello everyone will come back today. We will start a new chapter on this job of reporting aggregated data using the group function. It is also multi function. Now it's serious. We have two types of functions. The first time it's called Singularity functions and we already talk about all these singular functions. The other type called multiple rule functions and the multiple functions return one result. First set of fros or groups that single functions that return one result better group. Now I will start this chapter by Excel and I will show you the most important thing you should know. Now let's go to this Excel sheet. Suppose that I have a table on this table containing two columns the employee ID and the salary I have Employee ID 100. His salary is seven hundred. I have MP 1 0 1. His salary is 600 and I have 3 1 0 to his salary 600 and I have employee 1 0 3 and the salary is not. He doesn't have salary yet so the aggregate it functions or the functions is very simple. For example the minimum salary. What is the minimum salary in this table. It's very simple. It is 600. So you will look what is the minimum. Now the Merks salary what is the max says that it is seven hundred now. What does that count of salary. The Count of solely P3. This is one. This is true. This is a three so that can't ignore null values. So when I make Council this record will not be calculated or not. Be as that count. OK very nice. Now lets say about the sum. What is the sum of salaries. It is 700 plus 600 plus 600. Thats it. So it will be one thousand nine hundred. So it also ignore the null values. So this value is another value will not be calculated in the assumptions regarding the average. It will be the same 700 plus 600 plus 600 divided by three. Its also ignore null values. So this will not be calculated in the average. Now lets say I make this count distinct salary in this way we will consider nonpublic values only. So it will be too because this is what will be counted as one and these two values 600 will be counted as one. So we will consider only non duplicate values. The last thing for example can stop the gold star count the rows in table including. So it will be four. So the count the stock it will count $0 in table including Nunn's including double digits. Thats it. This is the simple abbrev about the aggregated functions. Now let's go through the PD file. So what are the group functions group functions operate on set of rows to give one result Peligro. So I have a table employees. What is the maximum salary of the employee. I will look and I will see what is the maximum salary. Now the most important types of group functions like the average that count Max minimum some list that we will talk about de-listed later and we have the standard deviations and the variance actually the standard deviations and variance. We already took it in the school and it is statistics functions and calculation functions. So don't worry about these two functions because it's not a part of this exercise is it's not a part of the exam for that exam. 0 61 or 0 71. So just remember this. Now not another important not that will function is a place of the keyboard. You may have multiple functions separated by a comma. So you can make money aggregate function separated by comma and then select a statement. Now first thing the syntax for the function you will put the group functions. Then you can open up brackets. Then you can put this thing as a sure and shit or all that is like is all. So you need to put it at all then you will put the expansions. Now this is the guidelines for using the group functions. Distinct makes the functions consider only non duplicate values all make it consider every value including double because the default is 0 and therefore doesn't need to be specified the datatype for the functions in the expressions argument can be correct or Vark out to non-board on it. So it's not only for the number. It's gonna work for the Volkoff. Also for the number and for that. All the group functions ignore values. OK. Do substitute values for null values. Use that envy. And envy to these gays or D-CT function. And we will talk about this in the exercises. So I think this is a good. The aggregate it functions in the next list. We will do some exercises. So thank you for listening and see you next to you.