Basic DB Using SQL Server (Part 6)
Tags: database, sql
Published: 2020-05-14 11:57:46

Hey there! We have come to the 6<sup>th</sup> post of the series! This post will be about **Aggregations**. The topics we're covering today are...

1. [Basic aggregation functions](#aggr-func)
2. [Aggregation with `WHERE` clause](#where-clause)
3. [Aggregation with `GROUP BY` clause](#group-by-clause)
4. [`HAVING` VS `WHERE`](#having-v-where)
5. [`ORDER BY` clause](#order-by)

Before we explore each topic, let's take a look at what *Aggregation function* means. **Aggregation function** basically means a function where values of multiple rows are grouped together to form a single summary value. For example, you have a salary column and you have 100 rows with each person's salary. You want to know the *average* salary of this table. That is when we use the *aggregation function*.  

<a name="aggr-func"></a>
# 1) Basic Aggregation functions

Some of the aggregation functions that are widely used include `COUNT()`, `SUM()`, `AVG()`, `MIN()`, and `MAX()`. 

We are going to use the table `Part` from the database called `PremierProducts` to demonstrate each of the function. The table looks like this.   
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/8bnm6d9xul4e3bqoch7r.png)

## `COUNT()` 

`COUNT()` returns the number of *rows* that match the criteria. 

``` SQL
-- How to use COUNT --
SELECT COUNT(*) FROM Table_name
-- or --
SELECT COUNT(Column_name) FROM Table_name
-- or --
SELECT COUNT(DISTINCT Column_name) FROM Table_name
```

If you want to count every row in the table, you can write something like

``` SQL
SELECT COUNT(*) FROM Part;
-- or --
SELECT COUNT(Class) FROM Part; 
-- It doesn't matter which column we choose 
-- because every row in the column will be counted
```
The result is `10` because we have a total of 10 rows. 

However, if you want to count **only the unique value** among duplicates, you add the keyword `DISTINCT`. 

``` SQL
SELECT COUNT(DISTINCT Class) FROM Part;
-- using different column may yield a different reult!
```
The result is now `3` because there are only 3 unique `Class` in the table. 

## `SUM()`

`SUM()` returns total number (sum) of a numeric column.

For example, we want to find the total price of every part in the table
``` SQL
SELECT SUM(Price) FROM Part;
```
We will get `4504.74` as a result.

## `AVG()`

`AVG()` returns the average value of a numeric column.

For example, we want to find the average price of every part in the table
``` SQL
SELECT AVG(Price) FROM Part;
```
We will get `450.474` as a result. 

## `MIN()`

`MIN()` returns the smallest value of a column.

 We can use `MIN()` to find the minimum price of every part in the table
 ``` SQL
SELECT MIN(Price) FROM Part;
 ```
We will get `24.95` as a result. 

However, we can use `MIN()` in a non-numeric column as well. For example, 
``` SQL
SELECT MIN(Class) FROM Part;
```
This will return `AP` as the resul because the letter A comes before other letters. 

## `MAX()`

`MAX()` returns the largest value of a column.

This works similarly to `MIN()` but instead of returning the minimum value, it returns the maximum value.
``` SQL
SELECT MAX(Price) FROM Part; -- the result is 1390.00
SELECT MAX(Class) FROM Part; -- the result is SG
```

## Tips~
When we run the aggregation function (like above), the result will displayed as a new, untitled column. Like this.. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/sf5978moqwsor3bib975.png)

However, we can rename that column with alias `AS`! Like so
``` SQL
SELECT AVG(Price) AS average_price FROM Part        -- without space
SELECT AVG(Price) AS 'average price' FROM Part      -- with space
```
And the results are..   
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/v1cbp3mtghi7jrrxjnr0.png)


<a name="where-clause"></a>
# 2) Aggregation with `WHERE` clause

Like any other SQL queries, you can use `WHERE` clause with aggregation functions to specify a condition. 

``` SQL
SELECT COUNT(*) AS Count, SUM(Price) AS Total_Price, AVG(Price) AS Avg_price
FROM Part;
-- versus --
SELECT COUNT(*) AS Count, SUM(Price) AS Total_Price, AVG(Price) AS Avg_price
FROM Part
WHERE Class='AP';
```

The results of both queries are   
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/gqit2uhrw4bqqqq4rky2.png)

See the difference? The second query only includes the row that meets the criteria (class='AP').

<a name="group-by-clause"></a>
# 3) Aggregation with `GROUP BY` clause

`GROUP BY` is useful when we want to learn about each group's characteristics. Remember the last query where we ask for the number of element, the total price, and the average price from `Part`? What if we use `GROUP BY` with it?
``` SQL
SELECT COUNT(*) AS Count, SUM(Price) AS Total_Price, AVG(Price) AS Avg_price, Class
FROM Part
GROUP BY Class
```
As you can see below, instead of getting the information on the whole table, we get information of each `Class`.    

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/9jkletp4hwj24anv972x.png)

## `HAVING` clause

Sometimes we want to use filter the results of aggregate functions with `GROUP BY` clause, this is when `HAVING` comes into play. 

Now, let's use the previous query 
``` SQL
SELECT COUNT(*) AS Count, SUM(Price) AS Total_Price, AVG(Price) AS Avg_price, Class
FROM Part
GROUP BY Class
HAVING Class <> 'SG'    -- <> is the same as !=
```
The result of this query is the table below. The row which has `Class = 'SG'` isn't included in the table. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/jwjvkkcvw00y0p17r7m2.png)

<a name="having-aggr"></a>
We can also use aggregate functions with `HAVING` clause as well. 
``` SQL
SELECT COUNT(*) AS Count, SUM(Price) AS Total_Price, AVG(Price) AS Avg_price, Class
FROM Part
GROUP BY Class
HAVING AVG(Price) > 200     -- like so
```
The result will return a table that includes row that has an average price (of each class) larger than 200.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/fun6e3on8ef49y19owkh.png)

<a name="having-v-where"></a>
# 4) `HAVING` VS `WHERE`

The main difference between `WHERE` and `HAVING` clause is that `WHERE` clause is used to filter the row *BEFORE* grouping while `HAVING` is used to filter the row *AFTER* grouping.

This means we cannot use `HAVING` without `GROUP BY` clause. At the same time, we cannot use [aggregate function](#having-aggr) with `WHERE` clause. 

<a name="order-by"></a>
# 5) `ORDER BY` clause
This is an optional clause to display the results of your query in a sorted mamner. We will use types of arguments which are `ASC` and `DESC`. 

For example,
``` SQL
SELECT * FROM Part 
ORDER BY Class      -- you can add ASC or leave it blank like this
```
The result will return a table that is sorted by `Class` in an ascending order. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/7v0mghs8exf5u2izgwwb.png)

If we want to display the same result but in descending order, we can add `DESC` like so...

``` SQL
SELECT * FROM Part 
ORDER BY Class DESC
```
The result will be like so...

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/xz51nys07uzrz23ofvb8.png)

Moreover, we can even sort using more than one column! Let's sort the table above using `Class` and then `Price` in an ascending order. 

``` SQL 
SELECT * FROM Part 
ORDER BY Class, Price ASC   -- you can leave out the ASC argument
```
The result of the above query is the table below. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/f70rkrg9ls533oyax17p.png)


# EOF !!
We have come to the end of this post! The script to create `PremierProducts` database can be found [here](https://github.com/imrinzzzz/year3-database-lab/blob/master/lab3/PremierProducts.sql). There's an exercise if you want to test your knowledge [here](https://github.com/imrinzzzz/year3-database-lab/blob/master/lab6/aggregation-exercise.md) and as usual the SQL queries to the exercise can be found [here](https://github.com/imrinzzzz/year3-database-lab/blob/master/lab6/aggregation.sql).

I'll see you in the next post!