"Compass Basics" was retired on February 18, 2017.

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

Well done!

You have completed Reporting with SQL!

Sign up for Treehouse Back to Library

Counting Groups of Rows

2:21 with Andrew Chalkley

It’s often handy to group rows together to count them. For example you could answer the question "How many books are in each genre?"

To count aggregated rows with common values use the GROUP BY keywords:

SELECT COUNT(<column>) FROM <table> GROUP BY <column with common value>;

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

    We're now going to use the Count Function to count groups of rows that have 0:00
    things in common. 0:04
    For example, how many products of an each category. 0:05
    Well, we could start out with a query where we select the category and 0:09
    order it by the category. 0:13
    We could count all of these up manually and 0:16
    keep track of a tally of each category. 0:19
    But there is no need for that. 0:21
    I've shown you how to use the Count function to count all the rows in a table. 0:23
    I've also shown you how to use Count to count the results from a query. 0:28
    But for this example, we need to count the groups of rows by their category. 0:32
    For example, all of the clothing products, all of the electronics products and so on. 0:38
    We can do that by using the group by keywords. 0:44
    At first glance, this looks like the key word distinct. 0:50
    But there's a key difference. 0:53
    Distinct discards any duplicates. 0:56
    Where as group by, groups rows together so you can use functions like count. 0:58
    Let's update this query to include the product counts. 1:04
    Now, that shows us how many products are in each category. 1:12
    Let's recap what we've just learned. 1:16
    Let's start with keywords. 1:19
    DISTINCT is used to get the unique values in a column. 1:22
    It's used in the select portion of a query. 1:26
    This will use the full table as a source of data to obtain distinct values. 1:29
    You can narrow down the dates and get a distinct values from that. 1:35
    You can use a WHERE cause. 1:39
    The next sets of key words you have learned were GROUP BY, 1:42
    GROUP BY appears at the end of the statement. 1:46
    This allows you to run functions like counts 1:49
    on collections of rows grouped by a particular value in a column. 1:52
    Then you can use the function like COUNT to count all rows that are in each group. 1:57
    [SOUND] Finally, you've seen COUNT counting all rows and the subsets of rows. 2:02
    You've seen me build up my queries, starting with simple queries first, 2:09
    then progressively getting more complex. 2:13
    This is a technique you can use. 2:16
    You don't have to write out your whole query in one go. 2:17

    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