aggregate functions in dbms

ha students coming to the next topic in database management system is aggregate functions so what are the aggregate functions so aggregate functions or the calculations it's just nothing but a calculations calculation on a set of values and returns a single value so those functions you call it has aggregate functions if you take any function that function works calculate on a set of values and it returns a single value as a result and it also ignores this aggregate function whatever the function you are taken so that aggregate function ignore null values except count and it is used it is used with the group by group by clause of the Select statement so if you want to access this aggregate function we can use group by clause in the Select statement select that function name and you can write that query by using the group by clause so let us say different aggregate function so what are a great functions what or aggregate functions so which functions you call it as an aggregate function AVG averaged ma x max so the these words you call it as an aggregate function so the shortcuts you can use these AVG when you want to calculate the average of Ray any data any column so you just write AVG instead of complete average name we can use AVG as a function name average to find out the average of a given column and the max maximum for maximum you have to use the aggregate function as a max mean to find the minimum value we will use min s um some to find the total M total amount you count count star so these are the different aggregate functions that are used in the database management system so let us take one example with the help of the example we see how these functions will work so let us take an example so here I'm taking an example employee table employee table means the relational table relational name for this table is employee so for this employee you are having the set of fields means the attributes employee ID employee name and age city so let me write the values in that employee 1 0 0 1 suppose we take ABC as one name and his age is 21 29 and city is Pune suppose employee is 0-2 PQR is a name and 30 is his age Pune is the city employee 3 element 25 Mumbai and employee for it's a XYZ and his age is 24 and he is also from City Mumbai and employee 5 is Eid is 5 s tu and his age is 32 and he is from Bangalore so let me take another field for this is the salary so they are getting total how much of salary so we can work on those these fall on the aggregate functions we'll work on the salary twenty thousand thirty thousand five thousand four thousand and twenty five thousand so these are all their salaries for this particular person with employee ID age and they are relieved in this cities so this is a let work on this aggregate functions the first one is AVG what this function will look AVG so it returns the average of the data values so it takes the complete data values and it find the average on that so the syntax for this average is syntax so just you have to write this select is a statement structured query language statement select ABG and here you have to write the column name so always we have to work on the column not on the rows of the column so a VJ column name from you have to select the table name so this is a syntax for the AVG average now let us take one example so this is an example I had taken according with the help of this table I am just calculating the average select average from the column name the salary so in this column we have to find the average salary from employee table from employee so this is a syntax to calculate the average of a given column so what will be the output for this the output is ratio of salary is equal to how much 16800 so the total average of this column this table is the 16,800 now let us take the another example so next one is the second one max so what this max function will do it returns it returns the maximum value for a column so if you take any table in that table if you the column the particular column in that column a maximum value will return with the help of this max the syntax for this is select max column name you have to enter which column you you want to find out the maximum value column name from table name so example so in the previous table I'm going to select maximum of salary from employee from employee table then what would be the output for this so in the previous table maximum of salary so what is the maximum value here thirty thousand is a maximum value so the output is maximum of salary is equal to thirty thousand or else you can write another example like for finding out maximum I'm taking another column select maximum of age if you take age age from employee so then what will be the output for this so what is the maximum age here so the maximum age is 32 is a maximum age so answer is maximum age is equal to 32 the maximum value it will be displayed as an output now coming to the next function so what is the next aggregate function minimum what this function will return it returns the minimum value from a column from e : so this impacts were this is just like maximum instead of max you just write the minimum select minimum column name from table name okay then what would be the let's take the example so select minimum of salary so in the column name I am taking the salary from employee table so what is the minimum value in the previous example so in the salary column what will be the minimum value 4000 so 4000 is an output for this query means it it retiring the value 4000 from the database table output is minimum of salary is equal to 4000 so this is about the minimum aggregate function now let us see the next function so that is a sum next aggregate function is the sum so what does some aggregate function will do it returns this sum sum is nothing but addition it perform addition of want some particular data values values so the syntax for this sum is syntax so select some column name from table name so let me write the example by the syntax by taking the previous example select some column name salary I'm taking salary from employee table so this structured query language is just like a simple language the name itself saying we are just asking select some salary from employee and here we are mentioning some condition where city is equal to pune means if you want you can give the condition if if you didn't give the condition it calculate the total salary amount that is present in the table suppose for this syntax select some salary from employee where city is equal to pooling means only this city Pune it can Eclipse the salary so 20 thousand plus 30 thousand it gives it 50 thousand so the output for this is 50 thousand suppose if you didn't mention the condition select some salary from employee just you're saying you have to calculate all the values in the salary from employee table here you are not mentioning the condition where city is equal to Pune but I'm just writing please select some salary from employee so what will be the salary from employ you had to calculate have to make some on all these items so 20 plus 30 50 thousand 50 thousand plus 25 75 thousand twenty-five thousand plus five eighty thousand four eighty four thousand so you will get the output as eighty four thousand so based on the user requirement the user can access the data from database so you're just selecting the with the help of this aggregate function now coming to the next functions see count so what this function will do it returns total number of values in a given column so it's just returns total number of values in a given column means in each column how many total number of values that are present in a given column so let me explain that the syntax for this is select sorry count here you have to write the column name so which column you want to select you want to count the column name from table name so let me ride a example for this select count so column name I'm taking the employee ID from employee so from employee table I have to calculate the total number of her columns Mensa employee IDs will be present so here this is the employee ID so I had to calculate the total number of values in the in this particular column how many total total number of values that are present in the employee ID one two three four five so total number five employee IDs be there so the output for this is just count employee ID is equal to fine so the output for this is that this aggregate function is the fine so let us take the Mast aggregate function that is the count star so what this function will do it returns the number of rows in a table so previously whenever if you didn't mention the star whenever you say the count with empty braces it returns number of values in a particular column okay means it only returns the number of values that are present in a particular column whereas count star it returns the number of rows that are present in a table you're taking complete table and it is counting the number of rows where as found with empty braces only particular column how many values are inserted so that is the difference don't be confused whenever the star is there it calculate the number of rows that are present in the table whereas count with empty brackets indicates returns number of values in a particular column only concentrate on particular column star complete rows the syntax for this is select count star from table name so let me write the example by taking the previous table select count star from employee table so what are the total number of tables the rows that are present in this employ table 1 2 3 4 5 so total number of rows that are present in this is 5 so put is count star is equal to 5 so these are the different aggregate functions thank you

Channel: Education 4u

Views: 80,367

Rating: 4.8669624 out of 5

Keywords: aggregate functions in sql, aggregate function database

Id: I95z0pSOUU0

Length: 18min 51sec (1131 seconds)

Published: Mon Feb 19 2018