Complex Aggregate Function example in SQL-Server 2005 with SubQuery:

Complex Aggregate Function example in SQL-Server 2005 with SubQuery:

In general analysis and reporting purpose, sometime we need to find department name, total salary paid to each department, total number of project department is working for and average salary being paid to each department. This is data administrative department may frequently asked for. Let us have a practical example of the same with GROUP BY clause, AGGREGATE function and SubQuery. For the demonstration purpose, we need to create following three tables and have to insert some data in it.

Create Table Department

(

deptNo INT IDENTITY(1,1) NOT NULL,

deptName VARCHAR(15) NOT NULL,

Description VARCHAR(50) NULL

)

Create Table Employee

(

empNo INT IDENTITY(1,1) NOT NULL,

empName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL,

Salary INT NOT NULL

)

Create Table Project

(

projNo INT IDENTITY(1,1) NOT NULL,

projName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL

)

Create Table Department

(

deptNo INT IDENTITY(1,1) NOT NULL,

deptName VARCHAR(15) NOT NULL,

Description VARCHAR(50) NULL

)

INSERT INTO Department(deptName,Description)

SELECT 'MIS1','MIS1' UNION ALL

SELECT 'MIS2','MIS2'

Create Table Employee

(

empNo INT IDENTITY(1,1) NOT NULL,

empName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL,

Salary INT NOT NULL

)

INSERT INTO Employee(empName,deptNo,Salary)

SELECT 'Ritesh',1,50000 UNION ALL

SELECT 'Bihag',2,29000 UNION ALL

SELECT 'Rajan',1,30000 UNION ALL

SELECT 'Alka',2,19000

Create Table Project

(

projNo INT IDENTITY(1,1) NOT NULL,

projName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL

)

INSERT INTO Project(projName,deptNo)

SELECT 'A',1 UNION ALL

SELECT 'B',2 UNION ALL

SELECT 'C',1

Now, we are going to find department name, salary paid to each department, project – department is working for and last but not least average salary paid to each department in order to find efficiency of department.

SELECT Dept.deptname, emp.salary, prj.[Count],emp.salary/prj.[count] as 'AverageSalary/DEPT'

FROM Department Dept,

(SELECT deptNo, sum(salary) salary FROM Employee GROUP BY deptNo) emp,

(SELECT deptNo, count(*) [Count] FROM Project GROUP BY deptNo) prj

WHERE Dept.deptNo=emp.deptNo and emp.deptNo=prj.deptNo

Above query has used simple SUM and COUNT aggregate function with subquery as table. As I gave example for Aggregate function, let me write down something more for an Aggregate function for those who are new to it.

Aggregate function will return only single row with computed value which summarizes the original result set. It will not return all row in return set. When you use aggregate function first result set returns the row set based on FROM clause and WHERE condition and then filtered records only will be aggregated.

Aggregate functions are expressions so it is mandatory to give column name to it explicitly or else it will return NULL as a column name, if you observe we gave name to all aggregated column like ‘AverageSalary/DEPT’. If we wouldn’t have done that, it would have return NULL as column name.

Aggregate function will not handle precision of numeric column, it is completely based on the source column. However, you can convert the results to other data type as well.

Reference: Ritesh Shah