<commit_before>.. Copyright (C)  Google, Runestone Interactive LLC
   This work is licensed under the Creative Commons Attribution-ShareAlike 4.0
   International License. To view a copy of this license, visit
   http://creativecommons.org/licenses/by-sa/4.0/.


Ifs and Cases
=============

In SQL, just as in many languages, one of the most basic and useful concepts is
the if/else syntax. :ref:`Just as in Sheets<what_is_a_formula_painters>`, you
can use ``IF`` to return different values based on whether a condition is true.

For example, going back to the bikeshare data, suppose you are interested in 
counting the number of trips that are shorter than one hour and longer than one
hour. The duration column contains the duration of the trips in seconds, so you 
can use this to create a column that tells you whether the duration is over one 
hour (3600 seconds).


.. activecode:: bikeshare_count_trips_above_below_one_hour
   :language: sql
   :dburl: /runestone/books/published/ac1/_static/bikeshare.db

   SELECT
     IF(duration > 3600, TRUE, FALSE) AS is_over_one_hour,
     COUNT(*) AS n_trips
   FROM
     bikeshare_stations
   GROUP BY
     is_over_one_hour


The syntax of the ``IF`` statement is
``IF(condition, value if condition is true, value if condition is false)``. This
should look familar, as it is exactly the same as the syntax for the ``IF``
function in Sheets.<commit_msg>file name is what_is_a_formula<commit_after>.. Copyright (C)  Google, Runestone Interactive LLC
   This work is licensed under the Creative Commons Attribution-ShareAlike 4.0
   International License. To view a copy of this license, visit
   http://creativecommons.org/licenses/by-sa/4.0/.


Ifs and Cases
=============

In SQL, just as in many languages, one of the most basic and useful concepts is
the if/else syntax. :ref:`Just as in Sheets<what_is_a_formula>`, you
can use ``IF`` to return different values based on whether a condition is true.

For example, going back to the bikeshare data, suppose you are interested in 
counting the number of trips that are shorter than one hour and longer than one
hour. The duration column contains the duration of the trips in seconds, so you 
can use this to create a column that tells you whether the duration is over one 
hour (3600 seconds).


.. activecode:: bikeshare_count_trips_above_below_one_hour
   :language: sql
   :dburl: /runestone/books/published/ac1/_static/bikeshare.db

   SELECT
     IF(duration > 3600, TRUE, FALSE) AS is_over_one_hour,
     COUNT(*) AS n_trips
   FROM
     bikeshare_stations
   GROUP BY
     is_over_one_hour


The syntax of the ``IF`` statement is
``IF(condition, value if condition is true, value if condition is false)``. This
should look familar, as it is exactly the same as the syntax for the ``IF``
function in Sheets.
{'fraction_non_alphanumeric': 0.07249466950959488, 'fraction_numerical': 0.00923951670220327, 'mean_word_length': 4.026785714285714, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4759307', 'n_tokens_mistral': 852, 'n_tokens_neox': 799, 'n_words': 378}