Concepts/Basic/

Arithmetic Operations

An introduction to the arithmetic operators +, -, *, \.

Arithmetic operators, +, -, *, and / are used to perform calculations on data being returned by a query and can be included in the SELECT, WHERE, and ORDER BY clauses.

Arithmetic operators can be used in the SELECT clause as a way of combining the values in different columns and presenting the results as a new column. This type of usage requires the AS keyword because we are creating a new column of data in our results for which there is no name in the original table.

For example, if we wanted to know how old the animals were at outcome we could look at the age_upon_outcome table. But the age values in it are the number of days old an animal was at outcome–not a particularly useful value for a 25 year-old animal. If we would like to see the ages in years, we could write a query like this:

SELECT monthyear,
       animal_type,
       outcome_type,
       (age_in_days / 365) AS `Years Old`
  FROM austin_animal_center_age_at_outcome
siyeh/austin-animal-center-statistics-oct-1-2013-to-dec-7-2017 Run query Copy code

The first several rows of the resulting table would look like this:

Monthyear animal_type outcome_type Years Old
2014-03 Dog Return to Owner 6.67
2014-12 Dog Return to Owner 7.45
2014-02 Dog Adoption 2.04
2014-01 Dog Adoption 3.02
2014-02 Dog Return to Owner 7.01
2014-08 Cat Adoption 0.31
2013-12 Dog Euthanasia 15.13
2016-06 Dog Return to Owner 14.01
2015-01 Dog Transfer 4.38
2014-07 Dog Transfer 3.50

Arithmetic operators can also be used in the WHERE clause to restrict the data returned.

SELECT monthyear,
       animal_type,
       outcome_type,
       (age_in_days / 365) AS `Years Old`
  FROM austin_animal_center_age_at_outcome
 WHERE (age_in_days / 365) > 8
siyeh/austin-animal-center-statistics-oct-1-2013-to-dec-7-2017 Run query Copy code
monthyear animal_type outcome_type Years Old
2013-12 Dog Euthanasia 15.13
2016-06 Dog Return to Owner 14.00
2014-02 Cat Euthanasia 15.01
2014-07 Dog Transfer 10.52
2013-11 Dog Return to Owner 10.95
2017-06 Dog Return to Owner 8.51

Arithmetic operators can also be used in the ORDER BY clause

SELECT monthyear,
       animal_type,
       outcome_type,
       (age_in_days / 365) AS `Years Old`
  FROM austin_animal_center_age_at_outcome
 WHERE (age_in_days / 365) > 8
 ORDER BY age_in_days
siyeh/austin-animal-center-statistics-oct-1-2013-to-dec-7-2017 Run query Copy code
monthyear animal_type outcome_type Years Old
2014-02 Dog Return to Owner 8.00
2015-01 Dog Return to Owner 8.00
2013-10 Cat Euthanasia 8.00
2013-10 Cat Euthanasia 8.00
2013-10 Dog Euthanasia 8.00
2013-12 Cat Euthanasia 8.00

Exercises (Continued from previous section)

There are two ways to do these exercises. The first is to use the “Try query” links to test your queries without saving them. The second is to create a data.world project and save your queries to it. If you are reading this documentation and completing the exercises as a tutorial, you will need to create your own project to save your work. Details and instructions are in the SQL tutorial which has instructions for setting up your project and links to all the current exercises.

Exercise 12

Using the patient, description, and value columns from the observations table, write a query that returns patient, description, and value. The entries in the value column need to be converted to lbs from kg (* 2.2), and the column should be relabeled as Weight in lbs.

You will get a warning that an expression of type ‘string’ was detected where ‘decimal’ was expected. This is because the values in the column are a mix of numbers and text. The records returned from this query are all numbers so the query works anyway.

Try query

Hint

SELECT the columns patient, description, and value * 2.2 labeled AS `Weight in lbs` FROM the observations table WHERE the column description = “Body Weight”.

See solution

Exercise 13

Write a query that returns the patient, description, and value columns from the observations table for the records where the patient is over 5’5” tall. The entries in the value column need to be converted from cm to inches (/ 2.5), and the column should be relabeled as “Over 5’5”.

Try query

Hint

SELECT the columns patient, and description, and value / 2.5 AS `Height in inches` FROM the observations table WHERE the column description = “Body Height” AND the column value / 2.5 is > 65.

See solution