17.4. Filtering

Filtering data is the process of restricting a query to a subset of rows based on some column condition. Maybe we only want to consider trips that start from a particular location. Or rides the have a certain duration.

To filter data with SQL, we use the WHERE keyword. You can append WHERE column_condition to any SQL query, and the result will be filtered only to rows that satisfy the column condition. For example, you might want to look only at bike trips which are at least one hour (3600 seconds):

Try running this and examining the duration column… the records that are returned by the SELECT should now all be ones with a duration of at least 3600.

When writing WHERE clauses, we use the same kinds of comparison operators that we do in Python: ==, !=, >, >=, … Just like in Python, we write numbers without commas like 3600. Also like in Python, if we want to write something that is a string, we have to put quotes around it (either single ' or double " quotes work). To select the records where member_type was Member we would write:

...
WHERE
    member_type == "Member"
    --Use quotes around Member because it is a string

Finally, notice that the WHERE clause is part of the query as the ; does not appear until after the WHERE. If there was a ; at the end of the FROM trip_data, the statement would end there and the WHERE would not be considered to be part of the rest of the query. Try putting a ; after trip_data and running the program. In the modified program, the SELECT is still valid and returns the 3 desired columns, but does it for trips of all durations. Then, SQL tries to interpret WHERE duration >= 3600 on its own, which makes no sense (what does it apply to???) and we get an error about the WHERE.

Warning

For the simple programs we write, there will always just be one query. You should only ever have one ; at the end of your program. You also can just leave off the ; entirely in which case your program is assumed to be all one statement.

It is also possible to filter by multiple criteria by using AND or OR. For example to look at bike trips which are 60 minutes or more and the member_type is MEMBER, the query would be as below.

You have attempted of activities on this page