Learn SQL: Microsoft SQL Server - Episode 6: Alias and Aggregate Data
Tags: sql, database, beginners, webdev
Published: 2021-10-30 21:57:27

![goran-greets-everyone](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c54n9myxbfuwixwwln1f.gif)

In this episode we will be discussing how to use **Aliases** in SQL queries and how to <u>aggregate</u> our data.

###Why Use Alias
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 <abbr title="SQL Management Studio">SSMS</abbr>.

![Skid-17](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/drn6l5gq6jk1mzhhyquq.gif)

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.

![cumbersome-query-statement](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kbc09owljgv2roj7h0ya.gif)

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.

![skid-14](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vb9swx9ingpxwfyt5z4a.gif)

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.

![alias-in-query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fp6txox24trcw1xeju1x.gif)

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.

![Skid-11](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l4mg247m95qh40c565tz.gif)

Notice how when we created an _alias_ for our tables, <abbr title="SQL Management Studio">SSMS</abbr> 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.

![alias-columns](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vawumeshivdg7xykls4y.gif)

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**_.

![skid-14](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vb9swx9ingpxwfyt5z4a.gif)

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.

![column-as-alias](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/alfxuakogbu1uemyar5j.gif)

![skid-4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/15pa2aop1q7kquktxjqq.png)

###Aggregate Data

We have several functions available that we can use to _aggregate_ data from our database.

####COUNT
**Count** allows us to _count_ the number of records that will be returned based on our query statement.

![count-function](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a0nh3vva0vwzqt0ancq2.gif)

We can even use this function with columns to determine how many <u>non NULL</u> values exist in the database. Meaning _that_ column we query should have a value.

![count-function-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jib5fc5tg6am2odv84a4.gif)

![skid-3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aydtgvkgoxecv3pfmsib.png)

####MIN
**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 <u>numbers and dates</u>.

To demonstrate **Min** we will use the **Sales.SalesOrderHeader** table and pull data from the **subTotal** column.

![Skid-17](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/drn6l5gq6jk1mzhhyquq.gif)

![min-query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/670jjkkc63vj4ekxod2y.gif)

![skid-4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/15pa2aop1q7kquktxjqq.png)

####Max
**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 <u>numbers and dates</u>.

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. 

![Skid-17](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/drn6l5gq6jk1mzhhyquq.gif)

![max-where-clause](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uc1qnbtzmmi10fk3wzyi.gif)

![skid-3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aydtgvkgoxecv3pfmsib.png)

####Group BY
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.

![Skid-17](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/drn6l5gq6jk1mzhhyquq.gif)

We start off with a **SELECT** statement and use the <u>COUNT(*)</u> function on the **Sales.SalesOrderHeader**. This will tell us the amount of orders in this table.

We now want to see the <u>order count</u> 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.

![group-by-query-statement](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wdcir1a4xpr2klpm27e3.gif)

Since we have **CustomerID** in our statement, we are trying to do the _count_ at the **CustomerID** level. However we need to explicitly tell <abbr title="SQL Management Studio">SSMS</abbr> this. We do this using **Group BY**.

![skid-14](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vb9swx9ingpxwfyt5z4a.gif)

**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.

![order-by-group-by](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k2ba3yut67vnt79h2v3e.gif)

We can now see which customers have how many orders, we can do the same query with **MIN** and **MAX**.

![Skid-11](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l4mg247m95qh40c565tz.gif)

We will start with **MIN** function to calculate the minimum **SubTotal**.

![order-by-group-by-min](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ke4fwvqwdxjhsqbl1e64.gif)

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 <u>Min Order Value</u> or <u>Max Order Value</u>.

![order-by-group-by-max-and-min](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9i0gdwoe2vc3bpafyi23.gif)

We can even chain this further by adding other _aggregated_ values such as **Count**.

![group-by-order-by-min-max-count](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3svy508jvru3b47nel6c.gif)

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 <u>total order count</u> by each sales person in each territory.

This means we are _aggregating_ at the <u>sales person</u> level and at the <u>territory</u> level. To do this we need to create a new query.

![Skid-11](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l4mg247m95qh40c565tz.gif)

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.

![total-orders-by-each-salesperson](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lncs6v5k9tlvpc6lm0qk.gif)

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.

![skid-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xbwg40nyxysh4dk25s02.gif)

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!

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5cbf38324eligaprddys.gif)