Basic DB Using SQL Server (Part 6)

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

1. Basic aggregation functions
2. Aggregation with WHERE clause
3. Aggregation with GROUP BY clause
4. HAVING VS WHERE
5. ORDER BY clause

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.

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.

COUNT()

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

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

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.

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

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

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

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,

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.

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

However, we can rename that column with alias AS! Like so

SELECT AVG(Price) AS average_price FROM Part        -- without space
SELECT AVG(Price) AS 'average price' FROM Part      -- with space

And the results are..

2) Aggregation with WHERE clause

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

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

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

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?

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.

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

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.

We can also use aggregate functions with HAVING clause as well.

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.

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 with WHERE clause.

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,

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.

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

SELECT * FROM Part 
ORDER BY Class DESC

The result will be like so...

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.

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.

EOF !!

We have come to the end of this post! The script to create PremierProducts database can be found here. There's an exercise if you want to test your knowledge here and as usual the SQL queries to the exercise can be found here.

I'll see you in the next post!