21 Group Functions

• ·         Calculates average value excluding nulls- AVG group function
• ·         Returns the number of rows with non-null values for the expression- COUNT  group function
• ·         For two sets of data with approximately the same mean, the greater the spread, the greater the standard deviation.- STDDEV group function
• ·         Operate on sets of rows to give one result per group- any group function
• ·         Returns minimum value ignoring nulls- MIN group function
• ·         Used with columns that store numeric data to calculate the spread of data around the mean- VARIANCE group function
• ·         Calculates the sum ignoring null values- SUM group function
• ·         Returns the maximum value ignoring nulls- MAX group function
• ·         To gather into a sum or whole- Aggregate functions return a single result row based on groups of rows. Aggregate is something that is formed by combining several separate elements.

1.  Define and give an example of the seven group functions: AVG, COUNT, MAX, MIN, STDDEV, SUM, and VARIANCE.

AVG,  COUNT :

·         Calculates average value excluding nulls- AVG group function

·         Returns the number of rows with non-null values for the expression- COUNT  group function

SELECT  AVG(salary) || ' is Average Salary of ' || COUNT(salary) || ' employees. This table has primary key employee_id which won''t be null. So, ' || (COUNT(employee_id) - COUNT(salary)) || ' rows are skipped in  Average Salary calculation.' "Example"

FROM EMPLOYEES;

 (I may use COUNT(*) instead of COUNT(employee_id), if I want to)

Output:

8775 is Average Salary of 20 employees. This table has primary key employee_id which won't be null. So, 0 rows are skipped in Average Salary calculation.

Please note that in this concatenation and minus has same precedence. So () were required.

MAX, MIN:

·         Returns minimum value ignoring nulls- MIN group function

·         Returns the maximum value ignoring nulls- MAX group function

SELECT 'The maximum of ' || COUNT(salary) || ' salaries in employees table is ' || MAX(salary) ||'. The minimum of '|| COUNT(salary) || ' salaries in employees table is '|| MIN(salary) ||'.' "Example2"

FROM EMPLOYEES;

Output:

The maximum of 20 salaries in employees table is 24000. The minimum of 20 salaries in employees table is 2500.

STDDEV, VARIANCE:

·         Used with columns that store numeric data to calculate the spread of data around the mean- VARIANCE group function

·         For two sets of data with approximately the same mean, the greater the spread, the greater the standard deviation.- STDDEV group function

SELECT 'The standard deviation of ' || COUNT(salary) || ' salaries in employees table is ' || ROUND(STDDEV(salary), 4) ||'. The variance of '|| COUNT(salary) || ' salaries in employees table is '|| ROUND(VARIANCE(salary), 4) ||'.' "Example3"

FROM EMPLOYEES;

Output:

The standard deviation of 20 salaries in employees table is 5659.6331. The variance of 20 salaries in employees table is 32031447.3684.

If not rounded the values would have been inconvenient to read.

SUM:

·         Calculates the sum ignoring null values- SUM group function

SELECT 'The sum of ' || COUNT(salary) || ' salaries in employees table is ' || SUM(salary) ||'.' "Example4"

FROM EMPLOYEES;

Output:

The sum of 20 salaries in employees table is 175500.

2.  Create a query that will show the average cost of the DJs on Demand events.  Round to two decimal places.

SELECT ROUND(AVG(cost),2) as "Average Cost"

FROM d_events;

9000

3.  Find the average salary for Global Fast Foods staff members whose manager ID is 19.

SELECT TO_CHAR(ROUND(AVG(salary),2), '$999999.99') as "Average Salary"

FROM f_staffs

WHERE manager_id = 19;

$8.38

4.  Find the sum of the salaries for Global Fast Foods staff members whose IDs are 12 and 9.

SELECT TO_CHAR(ROUND(SUM(salary),2), '$999999.99') as "Total Salary"

FROM f_staffs

WHERE id in (12, 19);

$66.75

5.  Using the Oracle database, select the lowest salary, the most recent hire date, the last name of the person who is at the top of an alphabetical list of employees, and the last name of the person who is at the bottom of an alphabetical list of employees. Select only employees who are in departments 50 or 60.

SELECT MIN(salary) "lowest salary", MAX(hire_date) "most recent hire date", MIN(last_name) "top last name", MAX(last_name) "bottom last name"

FROM employees

WHERE department_id in (50, 60);

6.  Your new Internet business has had a good year financially. You have had 1,289 orders this year. Your customer order table has a column named total_sales. If you submit the following query, how many rows will be returned?

SELECT sum(total_sales)

FROM orders;

one

7.  You were asked to create a report of the average salaries for all employees in each division of the company. Some employees in your company are paid hourly instead of by salary. When you ran the report, it seemed as though the averages were not what you expected—they were much higher than you thought! What could have been the cause?

SELECT AVG(NVL(salary, hourly_rate* hrs_worked_in_yr  ))

This way the null fields beings ignored will also be counted in.

8.  Employees of Global Fast Foods have birth dates of July 1, 1980, March 19, 1979, and March 30, 1969. If you select MIN(birthdate), which date will be returned?

March 30, 1969

9.  Create a query that will return the average order total for all Global Fast Foods orders from January 1, 2002, to December 21, 2002.

SELECT 'Average of ' || COUNT(order_number) || '  orders is : ' || AVG(NVL(order_total, 0)) as "Average"

FROM f_orders

WHERE order_date BETWEEN TO_DATE('January 1, 2002', 'fmMonth DD, YYYY') AND TO_DATE('December 21, 2002', 'fmMonth DD, YYYY');

Average of 1 orders is : 103.02

(I may use COUNT(*) instead of COUNT(order_number), if I want to)

10. What was the hire date of the last Oracle employee hired?

SELECT MAX(hire_date) as "the last"

FROM employees;

29-Jan-2000

11. In the following SELECT clause, which value returned by the SELECT statement will be larger?

SELECT SUM(operating_cost), AVG(operating_cost)

SUM must be  be ‘equal or greater than’ average.

12. Refer to the DJs on Demand database D_EVENTS table:

Which clauses represent valid statements?

_______a.  FROM event_date

FALSE, this is a column. It will say ORA-00942: table or view does not exist

_______b.  SELECT SUM(cost)

TRUE

_______c.  SELECT SUM(event_date)

FALSE. This column is not a number.  ORA-00932: inconsistent datatypes: expected NUMBER got DATE

_______d.  SELECT description, AVG(cost) AS "Expense"

FALSE.  ORA-00937: not a single-group group function. Remove either description (to get single row output), or remove avg(cost)

_______e.  WHERE MIN(id) = 100

FASLE.  MIN is a group function.  ORA-00934: group function is not allowed here

_______f.  SELECT MAX(AVG(cost)

FALSE. ORA-00978: nested group function without GROUP BY

_______g.  SELECT MIN(event_date)

TRUE