9.6. Aggregation or Group By

One very powerful features of SQL is that it allows us to create summary information by grouping rows together. For example, we could ask ourselves how many bike trips were taken for each subscriber type, and which subscriber type has the most bike trips?

%%sql
SELECT
  member_type, COUNT(*)
FROM
  trip_data
GROUP BY
  member_type
ORDER BY
  COUNT(*) DESC
LIMIT
  10
 * sqlite:///bikeshare.db
Done.
member_type COUNT(*)
Member 979814
Casual 246949
Unknown 4

GROUP BY member_type takes all the rows with a given member_type and produces a single row in the result. This means that we need to tell SQL how we want to combine the other columns’ values into a single row. The above example uses COUNT(*) which reports the number of rows that were combined.

Aggregating the values for member_type is not hard, since they’re all the same, SQL just gives us a single copy of the publisher name. Other columns, we need to either ignore (causing them to be omitted from the output) or specify a way to aggregate them.

We must specify an aggregate function for any column that we SELECT in our query (except the column that we’re grouping by) in order for the command to succeed. If we don’t specify a way to aggregate the value most database servers will complain. However, SQLITE does not. SQLite lets you do silly things without giving you an error. For example, the following query will work, but you have no idea what the results actually mean.

%%sql

SELECT
  duration, count(*)
FROM
  trip_data
GROUP BY
  member_type
ORDER BY
  COUNT(*) DESC
duration count(*)
0 3548 979814
1 346 246949
2 501 4

Here you have grouped by member_type, but without member_type in the select clause you have no idea which rows correspond to which member type. That is why most databases will flag this as a error. Furthermore the duration field may be the first duration in the group or maybe the last duration in the group or possibly in between, But its not defined. The best practices for writing group by queries that work well across database systems are as follows:

Let’s go back briefly to the first query in the Aggregation section. The top result was the count of bike trips for member_type Member:

member_type

COUNT(*)

Member

979814

If you’d like to get a more granular break down of the count, you may specify multiple columns to aggregate within the GROUP BY clause, for example: further breakdown the aggregate count by the start station IDs:

%%sql

SELECT
  member_type, start_station, count(*)
FROM
  trip_data
WHERE
  member_type = 'Casual'
GROUP BY
  member_type, start_station
ORDER BY
  COUNT(*) DESC
LIMIT
  20
 * sqlite:///bikeshare.db
Done.
member_type start_station count(*)
Casual 31200 10922
Casual 31217 10912
Casual 31235 9829
Casual 31219 8736
Casual 31225 7180
Casual 31228 6111
Casual 31222 5943
Casual 31215 5224
Casual 31201 4991
Casual 31218 4960
Casual 31237 4906
Casual 31232 4905
Casual 31623 4853
Casual 31205 4751
Casual 31613 4162
Casual 31212 4029
Casual 31238 3920
Casual 31104 3908
Casual 31203 3772
Casual 31204 3675

Great! Now that you’re familiar with how to aggregate data using SQL query by using COUNT() as your aggregation function, let’ take a look at other aggregation functions.

There are many such functions. Some common ones include:

So we could for example compute the total number of minutes of all bike trips for all subscriber types

%%sql

SELECT
  member_type, SUM(duration)
FROM
  trip_data
GROUP BY
  member_type
LIMIT
  10
 * sqlite:///bikeshare.db
Done.
member_type SUM(duration)
Casual 687530197
Member 759503541
Unknown 3434

9.6.1. Practice Exercises

Q-1: Compute the average duration of bike trips for each starting station id and list the results in order of highest average to lowest average for the 10 stations with the highest average. What is the highest average duration?

Q-2: What is the bike_number and count of the bike with the most rides?

Q-3: How many total rides by Members and Casual users

Q-4: What is the station that has the most rides that start and end at the same station? How many rides started there?

Lesson Feedback

    During this lesson I was primarily in my...
  • Comfort Zone
  • Learning Zone
  • Panic Zone
    Completing this lesson took...
  • Very little time
  • A reasonable amount of time
  • More time than is reasonable
    Based on my own interests and needs, the things taught in this lesson...
  • Don't seem worth learning
  • May be worth learning
  • Are definitely worth learning
    For me to master the things taught in this lesson feels...
  • Definitely within reach
  • Within reach if I try my hardest
  • Out of reach no matter how hard I try
You have attempted of activities on this page
Next Section - 9.7. Joining