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:
If you wanted to summarize all of the salaries in this table, you could use the following query:
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:
WHERE DepartmentID = 1
GROUP BY clause
This would produce a result of 75000. This all makes perfect sense, right? Now Im going to introduce you to a new clause in Transact-SQL. It is the GROUP BY 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:
Normally, if you specify a GROUP BY clause, you also specify an ORDER BY 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
GROUP BY DepartmentID ORDER BY DepartmentID
The above statement produces these results:
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
GROUP BY DepartmentID
ORDER BY DepartmentID