4.4 Nested Group Operations

The examples discussed in this chapter so far all involved one group operation on a column of a table. SQL also allows you to nest group functions, which means that one group function can enclose an expression that is itself a group operation on another expression or column. Let's consider the following example:

An economic slowdown has resulted in budget constraints for many employers, especially in the IT industry. Budget constraints have forced companies to take a second look at the money spent on employee compensation. Some companies have had to downsize their workforce, others have had to cut down employee bonuses, while still others have cut the employee base salaries. Your company is no exception, and is also under financial pressure. Your CEO must take a serious look at the compensation structure of the employees at various levels in various departments in the company.

Your CEO calls on you to query the Human Resources database and help him collect data. By this time, you've already heard the rumors of upcoming "reductions in force" (RIFs) and compensation cuts. This is your golden opportunity to impress your CEO with your skills, to make sure you are not affected by the RIFs. Mess up now, and you can be pretty sure that you have to start looking for another job in this increasingly competitive job market.

Here's your CEO's first question: What is the maximum amount of money spent by any department on employee salaries?

To answer this question, you know that you need to compute the sum of the salaries of all the employees in each department, and then find the maximum of those individual sums. Now that you know about the GROUP BY clause, finding the sum of salaries for all the employees in each department is easy:

SELECT dept_id, SUM(salary) 

FROM employee 

GROUP BY dept_id;



   DEPT_ID SUM(SALARY)

---------- -----------

        10        8750

        20        9900

        30        9400

However, your task is half done. You next need to find the maximum of the SUM(salary) values returned by this query. One way to do that is to use the preceding query as an inline view. (Inline views are discussed in detail in Chapter 5.) The following SELECT takes the results from the earlier query, which is now a nested query, and applies the MAX function to retrieve the highest SUM(salary) value:

SELECT MAX(sal) FROM

(SELECT dept_id, SUM(salary) sal 

FROM employee 

GROUP BY dept_id);



  MAX(SAL)

----------

      9900

However, you don't even need to write a subquery. Another, simpler way of writing the query you need is:

SELECT MAX(SUM(salary)) 

FROM employee 

GROUP BY dept_id;



MAX(SUM(SALARY))

----------------

            9900

The MAX(SUM(salary)) in this query is a nested group operation. When the query executes, the rows are aggregated by department. The innermost group function, in this case the SUM function, is used to generate one salary value per department. This is no different than in previous examples, but this time you have a MAX function seemingly left over. That MAX function is applied to the entire collection of SUM(salary) values produced by the initial aggregation. The result is a single value, the maximum amount of money that any one department spends on employee salaries, which is just what your CEO wanted.

It never makes sense to use more than one level of nested group function. MAX(SUM(salary)) returns a single value. One group function enclosing another will always return a single value. No further aggregation is possible.

Knowing only the maximum of the total salaries paid by a department isn't going to help much. So, your CEO's next question is to ask about the minimum and average amounts of money spent by any department on employee salaries. That should be an easy one to answer now. Just apply the same pattern as used in the previous query:

SELECT MIN(SUM(salary)), AVG(SUM(salary)) 

FROM employee 

GROUP BY dept_id;



MIN(SUM(SALARY)) AVG(SUM(SALARY))

---------------- ----------------

            8750             9350

Observing that the maximum (9900) is not too large compared to the minimum (8750) and the average (9350), your CEO realizes that all the departments spend pretty much uniformly on employee salaries. He next asks: What is the maximum, minimum, and average number of employees in any department? Use the following query to answer that question:

SELECT MAX(COUNT(*)), MIN(COUNT(*)), AVG(COUNT(*)) 

FROM employee 

GROUP BY dept_id;



MAX(COUNT(*)) MIN(COUNT(*)) AVG(COUNT(*))

------------- ------------- -------------

            6             3    4.66666667

The information that some departments have double the number of employees than some others may give your CEO some ideas about how he wants to reorganize the company and reduce cost. Hopefully, you have impressed your CEO with your SQL skills to the point that you are sleeping better at nights now, secure that you, at least, will still be employed in the morning.