Select Data  «Prev  Next»
 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:

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.