Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Reporting with SQL

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Calculating Averages

2:01 with Andrew Chalkley

Averages are great for calculating averages of scores, reviews and sales.

To get the average value of a numeric column use the AVG() function.

SELECT AVG(<numeric column>) FROM <table>;
SELECT AVG(<numeric column>) FROM <table> GROUP BY <other column>;

Cheat Sheets

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    There are so 0:00
    many helpful functions in SQL that helps us answer all sorts of questions. 0:01
    In this video we'll answer questions on averages. 0:06
    Let's say we wanted to forecast how much we could expect to 0:11
    make when we generate a set and amount of orders. 0:15
    What is the average amount people spend per order? 0:18
    Here's a simple query for 0:22
    selecting the cost at the time of purchase for all orders. 0:24
    We can use the aggregate function of average or AVG. 0:28
    Let's also provide an understandable label of average in our report used in an alias. 0:34
    When we run this, we get the average of all orders. 0:41
    If we had a particular target to generate a certain amount of revenue, 0:46
    this average could help us determine how many orders we need to achieve that goal. 0:51
    How about another question? 0:57
    What's the average order per customer? 0:59
    We can group by the user_id. 1:02
    Let's select the user_id as well. 1:09
    Okay, when we run this now we get all users average orders. 1:16
    This may help us to dive into people's spending habits. 1:21
    To recap, to get the average values 1:25
    you can use the AVG function on any numeric column. 1:28
    Not specifying your condition will do the full table. 1:33
    To find the average on a subset of records, you could add a where clause. 1:37
    For example, the average amount spent by each user or 1:42
    the average reviews for a particular product. 1:47
    In the next video, we'll explore some other mathematical functions 1:52
    like finding the maximum or minimum values for a given column or set of results. 1:55

    You need to sign up for Treehouse in order to download course files.

    Sign up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up