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

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
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
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