group by and aggregate function · csH sql walk routes

csH sql walk routes

Explore

group

Introducing Group by and aggregate functions

group by and aggregate functions are often used together to create summary results.

⁠

Suppose there was a functional requirement to display the number of routes which are available for each type of route, like this:

⁠

Loading…⁠

⁠

Both these columns are based on the routetype column in the route table, so:

select routetype, routetype from route

⁠

Loading…⁠

⁠

To get the required result we could

group together the values in the first column and then

count the number of rows in each group to get the second result

here is the SQL code

select routetype, count(routetype) as "number of routes"

from route

group by routetype

⁠

Loading…⁠

⁠

Display a table to show how many routes there are for each rating.

Display a table to show how many walks took place in the rain and in the dry.

What is the most popular route, (count the number of walks)?

What is the most popular type of route, (count the number of walks, join required)?

Largest group of people on each route.

Shortest distance for each type of route.

Total number of people who have walked each type of route.

Average minutes taken to complete each route.

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.