AGGREGATE FUNCTION: - Oracle Chennai



Multi-row Functions Group Functions Rules, SUM, MIN, MAX, COUNT, AVG Creating Groups of Data: GROUP BY Clause Filtering Group Results: The HAVING Clause 1. Group function accept only one arguments2. group function ignore null valuesAGGREGATE FUNCTION:Aggregate functions often need an added GROUP BY statement COUNT - counts the group SUM - totals the group AVG - averages the group MAX - finds maximum in the group MIN - find minimum in the group MAX:Max function is used to find max values in the tableWe can use the MAX function for any datatype q: find junior employee in the employees tablequery: Select max (hire_date) from employees Output: Max(hire_date) 21-APR-00MIN:Min function is used to find minimum values of table.We can use the min function for any datatypeQ: Find Senior employee in the employees tableQuery: Select min (hire_date) from employeesOutput: Min(hire_date) 17-jun-87Avg:Return the average value of the argumentIgnoring null valuesQuery: select avg(salary) from employees output: AVG(SALARY) ----------- 6461.68Sum:returns the sum or total the values of a column. Example: SELECT COUNT(employee_id), AVG(salary), SUM(salary) FROM employees; output: SUM(SALARY) ----------- 691400Count:Count (*) return the number rows including null values in a tableCount(commission_pct) returns the number of rows with non –null values of the expr Query: Select count(*) From employeesOutput: 107 (including null values)Groupby:If you include a group functions in a select clause , you cannot select individual results .Using a WHERE clause you can exclude rows before dividing them in to group You must include the columns in the group by clauseYou cannot include a column alias in the group by clausQUERY 1:select department_id,max(salary) from employees group by department_idoutput:DEPARTMENT_ID MAX(SALARY)100 120003011000 Using multiple columns in group by Q: The SQL query below shows the sum of SALARY within the group formed by a department and job ID Query:SELECT department_id, JOB_ID, SUM (SALARY)FROM employeesGROUP BY department_id ,JOB_IDOutput:DEPARTMENT_IDJOB_IDSUM(SALARY)--------------------------------------------------110Ac_account 8300 90 AD_VP 34000 Groupby ,HavingCondition based on group by function You cannot use the where clause to restrict groupsYou cannot use group function in the where clause SELECT <COLUMN LIST>, <GROUP BY FUNCTIONS>FROM <TABLE_NAME>WHERE <FILTER CONDITIONS>GROUP BY <COLUMN_LIST>HAVING <CONDITION BASED ON GROUP BY FUNCTIONS>query:Select department_id,avg(salary)From employeesGroup by department_idHaving avg(salary)>8000Output:DEPARTMENT_IDAVG(SALARY)10086009019333.3333333333333333333333333333333333209500701000011010150808955.882352941176470588235294117647058826 rows returned in 0.00 seconds????????Using order by:Query 1:Select salary from employeesorder by salary descoutput:salary240001300010000Query 2:Select job-id , sum(salary)from employeeswhere job-id not like ‘%Rep%’group by job_idhaving sum(salary)>13000order by sum(salary);output:JOB_IDSUM(SALARY)PU_CLERK13900AD_PRES24000IT_PROG28800AD_VP34000ST_MAN36400Nested:Group function can be nested to a depth of twoQuery: Select max(avg(salary))From employeesGroup by department_id Output:MAX(AVG(SALARY))19333.3333333333333333333333333333333333 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download