Skip to content
Gallery
csH sql walk routes
database tables
basic aggregate functions
group
reminder
More
Share
Explore
basic aggregate functions

Remember: select, from table, and *


Here is a very simple SQL command to display a table called: route
select * from route
Returns all rows from the route table and displays values for all columns.
The asterisk * causes select to display all the columns, there is no "where" clause so all the rows are displayed.

⁠
Loading…
⁠
⁠
You also can see that the display includes the number of rows returned: 6.

⁠

Introducing: Counting rows


Sometimes it is useful to know results like the number of rows rather than just the values in the table.

Requirement: How many routes are there?
⁠
Loading…
⁠
⁠

To display this result rather than the full table this SQL code is used.
select count(*) as "number of routes" from route
Compare this code to the simple select statement at the top of the page.
Two changes:
Use the count() function along with the asterisk.
Set an alias to a suitable heading for the result.

The code causes the all rows and columns to be fetched as usual but the rows are counted rather than simply displaying them.
The alias creates a suitable heading for the 6.
Notice that the final result is 1 row, not six rows as returned by the original command.

⁠

Introducing: Aggregate functions


Count is an aggregate function, it combines row (values) by counting them.
The other aggregate functions are SUM, MAX, MIN and AVG (average).
The other commands will be introduced soon.

⁠

Exploring the count function


Try this code which does not include an alias
select count(*) from route
The rows are counted by the heading is simply "count(*)", not very helpful.
That's why an alias is normally included.

Try this code ..
select routeid from route
.. and then include the count function
select count(routeid) from route
The rows are counted correctly.
There are still six rows even though there is only one column.
You can use one or more columns or use asterisk to use all of them, the number of rows will be the same.

You should remember that by using a "where" clause only matching rows will be shown
select rating from route
where rating = 2
How many rows were returned?
Now use the count function and an alias
select count(rating) as "grade 2 routes" from route
where rating = 2

Next

On the next page you can use the count function to produce results that meet the problem requirements.


Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.