Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Reporting with SQL!
You have completed Reporting with SQL!
Preview
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 upRelated 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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up