Group functions cannot be used in the WHERE clause: SELECT type_code FROM d_songs

4. Group functions cannot be used in the WHERE clause: SELECT type_code FROM d_songs WHERE SUM (duration) = 100; (this will give an error) ---------------------------------------------------------------------------------------- Group functions ignore NULL values. In the example below, the (null) values were not used to find the average overtime rate.

5. You can have more than one group function in the SELECT clause, on the same or different columns. You can also restrict the group function to a subset of the table using a WHERE clause. SELECT MAX(salary), MIN(salary), MIN(employee_id) FROM employees WHERE department_id = 60;

21. 19 rows exists in employees table from which only 7 are distinct (not repeated)

24. Number of rows in employees table 20 rows while the group function returns 4 rows that is not null values

28. GROUP BY Guidelines • Important guidelines to remember when using a GROUP BY clause are: • If you include a group function (AVG, SUM, COUNT, MAX, MIN, STDDEV, VARIANCE) in a SELECT clause and any other individual columns, each individual column must also appear in the GROUP BY clause. • You cannot use a column alias in the GROUP BY clause. • The WHERE clause excludes rows before they are divided into groups.

29. All column in select clause must be included in group by clause

30. NESTING GROUP FUNCTIONS Group functions can be nested to a depth of two when GROUP BY is used. SELECT max(avg(salary)) FROM employees GROUP by department_id; How many values will be returned by this query? The answer is one – the query will find the average salary for eachdepartment, and then from that list, select the single largest value.

37. ROLLUP In GROUP BY queries you are quite often required to produce subtotals and totals, and the ROLLUP operation can do that for you. The action of ROLLUP is straightforward: it creates subtotals that roll up from the most detailed level to a grand total, following a grouping list specified in the ROLLUP clause. ROLLUP takes as its argument an ordered list of grouping columns. First, it calculates the standard aggregate values specified in the GROUP BY clause. Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.

38. Rollup for department 10 Rollup for department 20 Rollup for department 50 Rollup for department 60 Rollup for department 80 Rollup for total departments in query Rollup for department 90

39. CUBE CUBE is an extension to the GROUP BY clause like ROLLUP. It produces cross-tabulation reports. It can be applied to all aggregate functions including AVG, SUM, MIN, MAX and COUNT. CUBE In the following statement the rows in red are generated by the CUBE operation: SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 50 GROUP BY CUBE (department_id, job_id)

42. GROUPING SETS The point of GROUPING SETS is that if you want to see data from the EMPLOYEES table grouped by (department_id, job_id , manager_id), but also by (department_id, manager_id) and also by (job_id, manager_id) then you would normally have to write 3 different select statements with the only difference being the GROUP BY clauses. For the database this means retrieving the same data in this case 3 times, and that can be quite a big overhead. Imagine if your company had 3,000,000 employees. Then you are asking the database to retrieve 9 million rows instead of just 3 million rows, quite a big difference. So GROUPING SETS are much more efficient when writing complex reports.

43. GROUPING SETS In the following statement the rows in red are generated by the GROUPING SETS operation: SELECT department_id, job_id, manager_id, SUM(salary) FROM employees WHERE department_id < 50 GROUP BY GROUPING SETS ((job_id, manager_id), (department_id, job_id), (department_id, manager_id))

46. Subquary can retrieve data from more than one table