Hey there! We have come to the 6th post of the series! This post will be about Aggregations. The topics we're covering today are...
- Basic aggregation functions
- Aggregation with
WHERE
clause - Aggregation with
GROUP BY
clause HAVING
VSWHERE
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
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!
Top comments (0)