I'm trying to build a rainbow, but I'm not sure I've got enough bricks of each
color. To check I could go through, select all the red bricks, and count how many of
them there are and repeat this with the orange, yellow, and so on. But this is
tedious. Luckily sequel can do this all for you.
Enter the world of aggregations.
Aggregate functions combine values from many rows and output a single row. These
enable you to do things like: tell how many rows you have, find the smallest and
biggest values in a column, or the total sum of all the values. Most aggregates
accept the column as an argument. But Ansi is slightly special. You can pass a
column to it, which gives you the number of non-null values in that column. Or, to
ensure you get the total number of rows, use star. They also allow you to prefix
the column with the keyword 'distinct'. This first filters your data set, to find
the different values for the column. Once it's done this, it then applies the function to
one instance of these values. For example, I've got four bricks here. We count them
the total is four. Also, if we count the color, that's four too. But there are only
two different colors so if we count the distinct colors, we only get
two. Similarly these two bricks are the same size and same weight, so if we were
to count the distinct weight of these bricks we'd effectively only get the
weight of three. Count, sum, min and max are some of the most common aggregate
functions that we use, but there are many more available in Oracle database to
aggregate your data. For a full list check out the links down
in the description, or if you have unusual requirements that these don't
cover, you can always write your own. So, to check how many bricks I have
available I can run a count, but this gives me the total number of bricks.
I need the number for each color. I could run a separate
query for each color to find out how many there are, but this is a lot of
work. Fortunately there's a shortcut. You can get the totals in a single query
using 'group by'. This splits up the results according to the values in the
group by column. You'll get a row in your output for each different value in it. So
a count group by color, will give you the number of different bricks of each. You
can group by as many columns or expressions as you want.
Doing so further subdivides your results, giving the totals for each set of values
in the table. This is great because I can check if I've got enough bricks of the
correct shape and color to build my rainbow. But if you want to know how many
bricks there are of each color, regardless of shape, you have a problem. Either you can count them
manually or run a separate query getting just the count by color. Once again
sequel comes the rescue with roll-ups. You place this in your group by, and then
put your columns within it. This then calculates the subtotals, working from
right to left through the columns. If all the grouping columns are in your roll-up,
it also gives the grand total. If you want the subtotals for every column in
the group by, use cube instead. When grouping, columns in the select must
either be in the group by clause or have an aggregate function applied. But the
reverse isn't true. You can group by columns not in your select. This can get
confusing, so in most cases copy your group by clause into the select. So I've
grouped my bricks by color, but I'm still not sure if I've got enough of each to
build my rainbow. I need at least ten of each color to do so. To find which
we've got enough of you may be tempted to do this filtering in your where
clause but databases group rows after the where clause so this doesn't work.
What you need to use instead is a 'having' clause. This filters the data
using the result of your aggregate functions. So to see which colors you've
got enough of add: having count star greater than or equal to ten. Before we
finish, it's important to remember that grouping and aggregation reduce the rows
in your output. But sometimes you want to see all the table data and the counts. You
can do this by converting your aggregate to an analytic function by adding the
'over' clause. But more on that another day. So we've looked at how you can select,
join and aggregate data from your tables, but this is no good if your tables are
empty. We'll look at how you can add data in the next episode.