- Counting Results 4:41
- Counting Results 2 objectives
- Counting Groups of Rows 2:21
- Counting Groups 2 objectives
- Getting the Grand Total 5:31
- Summing Values 1 objective
- Calculating Averages 2:01
- Averaging Values 1 objective
- Getting Minimum and Maximum Values 2:08
- Calculating the Minimum and Maximum Values 1 objective
- Performing Math on Numeric Types 4:35
- Performing Math 1 objective
- Practice Session 2:31
- Review: Aggregate and Numeric Functions 5 questions
Preview
Video Player
00:00
00:00
Counting Groups of Rows
2:21It’s often handy to group rows together to count them. For example you could answer the question "How many books are in each genre?"
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