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
- Aggregation with
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.
Some of the aggregation functions that are widely used include
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
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() 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() 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() 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() 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
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
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';
See the difference? The second query only includes the row that meets the criteria (class='AP').
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
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.
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.
The main difference between
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
GROUP BY clause. At the same time, we cannot use aggregate function with
This is an optional clause to display the results of your query in a sorted mamner. We will use types of arguments which are
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.
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!