Lesson 9	Aggregated data
Objective	Create queries to aggregate data.

SQL-Server Aggregated Data Queries

Aggregated Data Queries and SQL Data Selection

In this lesson, you will learn how to aggregate data in different potential situations. In this lesson, assume that you are working against the following table:

Employee table

EmployeeID	DepartmentID	Salary
101	1	50000
102	1	25000
103	2	60000
104	2	100000
105	2	125000

If you wanted to summarize all of the salaries in this table, you could use the following query:

SELECT SUM(Salary)
FROM Employees

The above query will produce the sum of all employee salaries, or 360000. However, what if you only wanted the sum of all employees in department 1? You could include a standard WHERE clause in the Transact-SQL statement, like this:

SELECT SUM(Salary)
FROM Employees
WHERE DepartmentID = 1

GROUP BY clause

This would produce a result of 75000. This all makes perfect sense, right? Now I’m going to introduce you to a new clause in Transact-SQL. It is the GROUP BY^[1] clause. A GROUP BY clause places Transact-SQL query results into groups that you specify. If you specify aggregate functions in your query, a GROUP BY clause will perform the aggregate function within the specified group and return a single value for each of the groups. The GROUP BY clause follows this general syntax:

1) The variable @LoopCount is declared as an integer.

3) All values in which EmployeeId equals 17 are retrieved from the Employer table.

Aggregate Data

ORDER BY clause

Normally, if you specify a GROUP BY clause, you also specify an ORDER BY^[2] clause (after the GROUP BY clause) so that the grouped data is also ordered. Generally, the GROUP BY and ORDER BY clause contain the same columns.

Aggregate query example

We will create a query that returns the SUM of all employees' salaries, but groups them for each department. Because we are selecting the SUM and not an actual column name, it is a good idea to use an alias so that the name of the column in the resultset is understandable. For this query to work, you would, of course, use the GROUP BY clause, like this:

SELECT SUM(Salary) AS Salary 
FROM Employees 
GROUP BY DepartmentID ORDER BY DepartmentID

The above statement produces these results:

Salary

75000

285000

For the above results, you do not know which SUM relates to which department because the query returned exactly what you asked for. Therefore, let us rewrite the query to return the department also, like this:
In the next lesson, you will learn how to construct queries within queries, also known as subqueries.

SELECT DepartmentID, SUM(Salary) AS Salary
FROM Employees
GROUP BY DepartmentID
ORDER BY DepartmentID

Now the results are this:

DepartmentID	Salary
1	75000
2	285000

Aggregate Data - Exercise

Click the Exercise link below to practice creating aggregate queries.
Aggregate Data - Exercise

[1]GROUP BY: Specifies how a query should break, based on values that change in the columns represented after the GROUP BY keywords.

[2]ORDER BY: SQL keywords that specify the order in which the data returned from a query will be returned.