Key Concepts

Review core concepts you need to learn to master this subject

Column References

SUM() Aggregate Function

MAX() Aggregate Function

COUNT() Aggregate Function

GROUP BY Clause

MIN() Aggregate Function

AVG() Aggregate Function

HAVING Clause

See more

Column References

SELECT COUNT(*) AS 'total_movies', 
   rating 
FROM movies 
GROUP BY 2 
ORDER BY 1;

The GROUP BY and ORDER BY clauses can reference the selected columns by number in which they appear in the SELECT statement. The example query will count the number of movies per rating, and will:

  • GROUP BY column 2 (rating)
  • ORDER BY column 1 (total_movies)
Aggregate Functions
Lesson 1 of 1
  1. 1
    Introduction
    We’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…
    Start
  2. 2
    Count
    The 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…
    Start
  3. 3
    Sum
    SQL 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…
    Start
  4. 4
    Max / Min
    The 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…
    Start
  5. 5
    Average
    SQL 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…
    Start
  6. 6
    Round
    By 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…
    Start
  7. 7
    Group By I
    Oftentimes, 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…
    Start
  8. 8
    Group By II
    Sometimes, 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…
    Start
  9. 9
    Having
    In 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…
    Start
  10. 10
    Review
    Congratulations! 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…
    Start

What you'll create

Portfolio projects that showcase your new skills

Pro Logo

How you'll master it

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

Pro Logo