18
Learn SQL: Microsoft SQL Server - Episode 6: Alias and Aggregate Data
In this episode we will be discussing how to use Aliases in SQL queries and how to aggregate our data.
| Alias | Alias |
|---|---|
| less code | readability |
When we use Aliases our SQL becomes more readable and easy to understand, plus we will also have better column names.
We can see what Aliases are in SSMS.
In this instance, we want to pull all the data with a BusinessEntityID greater than 2000, we want to Order BY, BusinessEntityID in ascending order and we want to see the BusinessEntityID in our result set, meaning we should include it in our SELECT query.
Notice how cumbersome it is, we need to specify the table we are referring to in each query statement. This is where aliases can help. Also if we want to give more meaningful names to any of the columns then aliases can also be useful.
We can give aliases to our columns and tables. We will start by giving aliases to all the tables we have used so far. For example instead of referring to Person.Person we can create an alias for it.
We use the AS keyword to create an alias, we can name it whatever we want but in most cases we would simplify it to make it easier to read and understand.
Notice how when we created an alias for our tables, SSMS no longer accepted the original table name and it threw an error in the form of the red underline. We had to change them to the alias name we gave our tables.
We can also create an alias for our columns in the same way we created an alias for our tables.
Notice in our result set, when we gave the firstName the alias of Fname it showed up in the result set. We can give our columns more meaningful names. Lets say for instance, we want to give our firstName column the alias of first name.
To do this we need to enclose the alias name in brackets or we need to enclose it inside double quotes, since it has a space in-between and SQL does not like that.
We have several functions available that we can use to aggregate data from our database.
Count allows us to count the number of records that will be returned based on our query statement.
We can even use this function with columns to determine how many non NULL values exist in the database. Meaning that column we query should have a value.
Min allows us to return the minimum value for the specified column for that table.
Min can be used in any type of column, but most of the time it is used for numbers and dates.
To demonstrate Min we will use the Sales.SalesOrderHeader table and pull data from the subTotal column.
Max allows us to return the maximum value for the specified column for that table.
Max can be used in any type of column, but most of the time it is used for numbers and dates.
We can use the Max function in the same way as the Min function, We can also use a Where clause to limit the return data by a particular date, ID or customer or anything we want to use.
What if we had to look at Count of records for a particular column, lets say we want to see how many orders have each customer placed, or the Min or Max value of orders placed by each customer.
Group By can help us to accomplish this task.
We start off with a SELECT statement and use the COUNT() function on the **Sales.SalesOrderHeader*. This will tell us the amount of orders in this table.
We now want to see the order count by each customer, naturally we would add the CustomerID either before or after the Count(*) function in the Select statement. If we hit execute we get an error, the reason is we need to specify at what level are we counting the records at.
Since we have CustomerID in our statement, we are trying to do the count at the CustomerID level. However we need to explicitly tell SSMS this. We do this using Group BY.
Aggregated columns will show no column names in all our SQL statements. This is where an Alias is useful, by allowing us to give our aggregated data meaningful names.
We can even sort this list. Let's order the list by the alias in ascending and then in descending order.
We can now see which customers have how many orders, we can do the same query with MIN and MAX.
We will start with MIN function to calculate the minimum SubTotal.
We can use the MAX function in the same statement. We use the Order By function to decide how we want to sort the result set, either by Min Order Value or Max Order Value.
We can even chain this further by adding other aggregated values such as Count.
Thus far we have looked at aggregating by one column, However in any real case scenarios, we might need one or more columns to aggregate the data at. Let's say we want to find out the total order count by each sales person in each territory.
This means we are aggregating at the sales person level and at the territory level. To do this we need to create a new query.
We will still be working in the Sales.SalesOrderHeader table, if we have a peek at the columns within we will notice the SalesPersonID and TerritoryID columns. We will be using these columns in our query statement.
We can also order the result set of this query statement by the SalesPersonID. From the result set we can see the breakdown of each sales persons sales in each territory.
How cool was that, we were able to do some awesome things with just the basics of SQL to pull data from our database that is insightful and easy to understand.
I really hope you enjoyed it, stay zen folks!
18