| 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:
|EmployeeID ||DepartmentID ||Salary |
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 I’m going to introduce you to a new clause in Transact-SQL. It is the GROUP BY
clause places Transact-SQL query results into groups that you specify. If you specify aggregate functions in your query, a
clause will perform the aggregate function within the specified group and return a single value for each of the groups. The
clause follows this general syntax:
ORDER BY clause
Normally, if you specify a
clause, you also specify an ORDER BY
the GROUP BY
clause) so that the grouped data is also ordered.
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
Now the results are this:
Aggregate Data - Exercise
Specifies how a query should break, based on values that change in the columns represented after the GROUP BY keywords.
SQL keywords that specify the order in which the data returned from a query will be returned.