Congratulations to Nico De Greef, a software architect for Denco in Belgium, and Zivan Karaman, the head of the biostatistics unit for Limagrain in France. Nico won first prize of \$100 for the best solution to the June Reader Challenge, "Averaging Employee Salaries." Zivan won second prize of \$50. Here’s a recap of the problem and the solution to the June Reader Challenge.

### Problem:

Jon develops and maintains a SQL Server 2000 database for his company’s human resources department. The database contains a view that provides details about each employee in the company by department and branch. Jon needs to write a report that provides the average employee salary in each branch and department and the sum of average employee salary (as calculated in the previous step) for each branch. Rows from the query must be ordered by branch, department, and summary information. Help Jon write the query efficiently. The following table represents a sample of the data as queried from the view:

`CREATE TABLE BranchDeptEmpSal (                                BranchId  char(3) NOT NULL,                                DeptId int NOT NULL,                                EmpId int NOT NULL,                                Salary money  NOT NULL                              )                              INSERT INTO BranchDeptEmpSal VALUES( '001', 1, 1, 1000 )                              INSERT INTO BranchDeptEmpSal VALUES( '001', 1, 2, 500 )                              INSERT INTO BranchDeptEmpSal VALUES( '001', 1, 3, 600 )                              INSERT INTO BranchDeptEmpSal VALUES( '001', 2, 4, 2000 )                              INSERT INTO BranchDeptEmpSal VALUES( '001', 2, 6, 1600 )                              INSERT INTO BranchDeptEmpSal VALUES( '002', 1, 11, 400 )                              INSERT INTO BranchDeptEmpSal VALUES( '002', 1, 12, 800 )                              INSERT INTO BranchDeptEmpSal VALUES( '002', 2, 8, 400 )                              INSERT INTO BranchDeptEmpSal VALUES( '002', 2, 10, 600 )`

### Solution:

Jon obtains the average salary of employees in each branch and department by using the following query:

`SELECT BranchId, DeptId, AVG( Salary ) AS AvgSal                                 FROM BranchDeptEmpSal                                GROUP BY BranchId, DeptId`

Then, Jon uses this query as the source for another SELECT statement and uses the ROLLUP operator to get the sum of the average salaries for each branch. Jon uses the ROLLUP operator to generate summary rows based on the grouping that the GROUP BY clause provides. The following SELECT statement incorporates the ROLLUP operator:

`SELECT BranchId, DeptId, AvgSal, SUM( AvgSal ) AS SumAvgSal                                FROM (                                 SELECT BranchId, DeptId, AVG( Salary ) AS AvgSal                                  FROM BranchDeptEmpSal                                 GROUP BY BranchId, DeptId                                ) AS b                               GROUP BY BranchId, DeptId, AvgSal                               WITH ROLLUP `

The query’s ROLLUP operator generates summary rows at each hierarchy level based on the GROUP BY clause’s column order. To get only summary information about the sum of the average salaries at the branch level, Jon tests for that condition by using the GROUPING() function in the HAVING clause. The GROUPING() function takes a column name as input, then returns 1 if the ROLLUP operator generated the row. Here’s the query that incorporates the GROUPING() function in the HAVING clause to output only the desired rows from the ROLLUP operator:

`SELECT BranchId, DeptId, SUM( AvgSal ) AS SumAvgSal                                FROM (                                 SELECT BranchId, DeptId, AVG( Salary ) AS AvgSal                                  FROM BranchDeptEmpSal                                 GROUP BY BranchId, DeptId                                ) AS b                               GROUP BY BranchId, DeptId                               WITH ROLLUP                              HAVING GROUPING( BranchId ) = 0`

Finally, Jon puts the results in order by specifying the query’s ORDER BY clause, then uses the GROUPING() function to output the summary at the branch level. The following complete query includes the ORDER BY clause:

`SELECT BranchId, DeptId, SUM( AvgSal ) AS SumAvgSal                                FROM (                                 SELECT BranchId, DeptId, AVG( Salary ) AS AvgSal                                  FROM BranchDeptEmpSal                                 GROUP BY BranchId, DeptId                                ) AS b                               GROUP BY BranchId, DeptId                               WITH ROLLUP                              HAVING GROUPING( BranchId ) = 0                              ORDER BY BranchId, GROUPING(DeptId), DeptId`