SQL lets you process the data of the tables you retrieve. Aggregate functions give the simplest form of SQL data processing, returning a single metric over a column.

The most common aggregate functions are:

• AVG: returns the mean of the values in the column
• SUM: returns the sum of the values in the column
• COUNT: returns the number of values in the column
• MAX: returns the highest or alphabetically last value in a column
• MIN: returns the lowest or alphabetically first value in a column

For example, if we have a products table:

Then, the following query would get us the average price of all products:

We can also rename the result of an aggregate function just as we did for columns. So, for example, the output for

SELECT MIN(price) AS lowest_price FROM products

would be:

The MAX and MIN clauses also work for alphabetical orders, so

SELECT MIN(name) FROM products

would return:

Numerical operations

Another simple way of processing the output is through applying the same operation to all elements in a numerical column.

For example:

SELECT price + 5 AS price_plus_shipping FROM products

will return the renamed price column, adding 5 to each element:

If we wanted to show different prices after a discount has been applied, we could use the following query:

SELECT id, name, price * 0.8 AS price_with_discount FROM products

and get this result:

Now that you have learned about aggregate functions and numerical operations, you can begin answering questions on your data using SQL!

What we learned so far:

When starting with SQL, the execution order of different clauses may be hard to grasp.

That’s why we’ve decided to end the introductory lessons with example queries that use clauses we’ve learned up to that point, and then show the order of execution for the actions they perform.

Example queries:

SELECT first_name, salary + 200
FROM employees
WHERE salary > 7500 
AND last_name != "Williams"

SELECT SUM(salary + 2)*3, COUNT(id)
FROM employees
WHERE date_hired > 2019-03-14

Execution order

1. FROM -> Gets input table
2. WHERE -> Filters rows according to condition
3. SELECT -> Selects the columns we need.
4. SUM , COUNT, +, * etc. -> Aggregate functions and numerical operations performed on output.