## Key Concepts

Review core concepts you need to learn to master this subject

SQL aggregate functions

SQL COUNT() aggregate function

Usage of SQL SUM() aggregate function

Finding average value of a column in SQL

Average value of a column in SQL

ROUND() function in SQL

GROUP BY clause in SQL

Grouping and Ordering selected columns by number

SQL aggregate functions

SQL aggregate functions

SQL aggregate functions perform a calculation on a set of values and return a single value:

`COUNT()`

`SUM()`

`MAX()`

`MIN()`

`AVG()`

SQL COUNT() aggregate function

SQL COUNT() aggregate function

The `COUNT()`

aggregate function in SQL returns the total number of rows that match a specified criteria. For instance, to find the total number of employees who have more than 5 years of experience, the above query can be used. A column name of the table can also be used instead of `*`

. Unlike `COUNT(*)`

, This variation `COUNT(column_name)`

will not count `NULL`

values for that column.

Usage of SQL SUM() aggregate function

Usage of SQL SUM() aggregate function

The `SUM()`

aggregate function in SQL returns the sum value of a column. For instance, to find the total disbursed `salary`

, the above query can be used.

Finding average value of a column in SQL

Finding average value of a column in SQL

The `AVG()`

aggregate function in SQL returns the average value in a column. For instance, to find the average amount of `salary`

for the employees having experience less than 5 years, the above query can be used.

Average value of a column in SQL

Average value of a column in SQL

The `AVG()`

aggregate function in SQL returns the average value in a column. For instance, to find the average amount of `salary`

for the employees having experience less than 5 years, the above query can be used.

ROUND() function in SQL

ROUND() function in SQL

The SQL `ROUND()`

function will round a number value to a specified number of places. It takes two arguments: a number, and a number of decimal places. It can be combined with other aggregate functions, as shown in the query above. This query will calculate the average rating of movies from 2015, rounding to 2 decimal places.

GROUP BY clause in SQL

GROUP BY clause in SQL

The SQL `GROUP BY`

clause will group records in a result set by identical values in one or more columns. It is often used in combination with aggregate functions to query information of like records. The `GROUP BY`

clause can come after `FROM`

or `WHERE`

but must come before any `ORDER BY`

or `LIMIT`

clause. The query above will count the number of movies per rating.

Grouping and Ordering selected columns by number

Grouping and Ordering selected columns by number

The SQL `GROUP BY`

and `ORDER BY`

clauses can reference the selected columns by number in which they appear in the `SELECT`

statement. The query above will count the number of movies per rating, and will `GROUP BY`

column 2 (`rating`

) and `ORDER BY`

column 1 (`total_movies`

) in the `SELECT`

statement.

HAVING clause in SQL

HAVING clause in SQL

The SQL `HAVING`

clause is used to further filter result set groups provided by the `GROUP BY`

clause. `HAVING`

is often used with aggregate functions to filter the result group based on an aggregate property. The query above will filter only years where more than 5 movies were released per year.

Finding largest value of a column in SQL

Finding largest value of a column in SQL

The `MAX()`

aggregate function in SQL returns the largest value in a column. For instance, to find the largest value of `Amt`

column from the table named `tbl_Transactions;`

, `SELECT MAX(Amt) from tbl_Transactions;`

query can be used.

Largest value of a column in SQL

Largest value of a column in SQL

The `MAX()`

aggregate function in SQL returns the largest value in a column. For instance, to find the largest value of the `amount`

column from the `transactions`

table, the above query can be used.

Finding smallest value of a column in SQL

Finding smallest value of a column in SQL

The `MIN()`

aggregate function in SQL returns the smallest value in a column. For instance, to find the smallest value of `Amt`

column from the table named `tbl_Transactions`

, `SELECT MIN(Amt) from tbl_Transactions;`

query can be used.

Smallest value of a column in SQL

Smallest value of a column in SQL

The `MIN()`

aggregate function in SQL returns the smallest value in a column. For instance, to find the smallest value of the `amount`

column from the table named `transactions`

, the above query can be used.

- 1We’ve learned how to write queries to retrieve information from the database. Now, we are going to learn how to perform calculations using SQL. Calculations performed on multiple rows of a table a…
- 2The fastest way to calculate how many rows are in a table is to use the COUNT() function. COUNT() is a function that takes the name of a column as an argument and counts the number of non-empty va…
- 3SQL makes it easy to add all values in a particular column using SUM(). SUM() is a function that takes the name of a column as an argument and returns the sum of all the values in that column. Wh…
- 4The MAX() and MIN() functions return the highest and lowest values in a column, respectively. How many downloads does the most popular app have? SELECT MAX(downloads) FROM fake_apps; The most p…
- 5SQL uses the AVG() function to quickly calculate the average value of a particular column. The statement below returns the average number of downloads for an app in our database: SELECT AVG(downl…
- 6By default, SQL tries to be as precise as possible without rounding. We can make the result table easier to read using the ROUND() function. ROUND() function takes two arguments inside the parenth…
- 7Oftentimes, we will want to calculate an aggregate for data with certain characteristics. For instance, we might want to know the mean IMDb ratings for all movies each year. We could calculate ea…
- 8Sometimes, we want to GROUP BY a calculation done on a column. For instance, we might want to know how many movies have IMDb ratings that round to 1, 2, 3, 4, 5. We could do this using the follow…
- 9In addition to being able to group data using GROUP BY, SQL also allows you to filter which groups to include and which to exclude. For instance, imagine that we want to see how many movies of di…
- 10Congratulations! You just learned how to use aggregate functions to perform calculations on your data. What can we generalize so far? - COUNT(): count the number of rows - SUM(): the sum of the v…

## What you'll create

Portfolio projects that showcase your new skills

## How you'll master it

Stress-test your knowledge with quizzes that help commit syntax to memory