Averaging Employee Salaries

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 ROLLUPHAVING 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 ROLLUPHAVING GROUPING( BranchId ) = 0ORDER BY BranchId, GROUPING(DeptId), DeptId

JULY READER CHALLENGE:

Now, test your SQL Server savvy in the July Reader Challenge, "Disabling Protocols" (below). Submit your solution in an email message to [email protected] by June 17. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

Sean is a systems administrator in a corporate IT department. He recently received some SQL Server security bulletins about attacks that exploit vulnerabilities in various network protocols. Sean wants to update each SQL Server 2000 installation in the network with the necessary patches. As an added security measure, Sean also wants to disable unnecessary protocols on the server. However, in Sean’s company, the standard protocol for communications between SQL Server and clients is TCP/IP. Help Sean write a script that disables all protocols except TCP/IP for his SQL Server installation.