Aggregate Functions (SQL)

In the medium post, we aim to explore aggregate functions and how they can help us sort through our data easily.

Learning Objectives:

• Describe and use various aggregate functions.
• Explain how each aggregate function can help you analyse data.
• Use various aggregate functions to help summarise and analyse data.
• Describe and use the DISTINCT function.

Aggregate functions are simple to use but extremely powerful. They can be used to summarise data, find the highest values, lowest values, the total number of rows and average values across your data.

Aggregate Functions.

• AVG() averages a column of values.
• COUNT() counts the number of values.
• MIN() finds the minimum value.
• MAX() finds the maximum value.
• SUM() sums the column values.

Here are the aggregate functions we can use.

AVG() Function.

Rows containing NULL are ignored by the average function.

SELECT AVG(unit_price) AS avg_price
FROM products

Count() Function.

This can be useful to use when we want to know the contents of a table.

Below we count all the rows containing values or NULL values. This is because an * counts all rows and does not take exception to NULL values.

SELECT COUNT(*) AS
total_customers
FROM customers

However, here we count all rows excluding NULL values. This is because we are specifying our column name.

SELECT COUNT(CustomerID) AS
total_customers
FROM customers

MAX() and MIN() Functions.

Again, NULL values are ignored when using these functions.

Here are some examples using both:

SELECT MAX(unit_price) AS max_prod_price
FROM products

Here is how we would use MIN():

SELECT MIN(unit_price) AS min_prod_price
FROM products

We could also combine them together like this:

SELECT MAX(unit_price) AS max_prod_price,
MIN(unit_price) AS min_prod_price
FROM products

SUM Aggregate Function.

Here we will use the SUM() function.

SELECT SUM(unit_price) AS total_prod_price
FROM products

Or if we wanted to get the total_price as a multiplication value based on the sum or unit_price and units_in_stock we could:

SELECT SUM(unit_price*units_in_stock) AS total_price
FROM products
WHERE supplier_id = 23;

Using Distinct on Aggregate Functions.

If DISTINCT is not specified, all the data is assumed. Even duplicate values.

So, if we wanted a true reflection of the number of unique customers we have, we could use the following:

SELECT COUNT(DISTINCT customer_id)
FROM customers

I would also love to connect on Twitter, LinkedIn or on my Website. I am not sure what value I could provide you with but if you reach out, I would be more than happy to have a conversation or talk about what I have written here.