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