Search code examples
sqlgoogle-bigquerypercentagecalculated-columns

How to calculate percent change between two columns in SQL?

enter image description here

Currently learning SQL (using BigQuery) and I'm trying to calculate the percentage change between values from two different columns (ridership_2013 and ridership_2018 - both columns are integers). Based on the screenshot above, here is what I attempted to do:

select station_name, ridership_2013, ridership_2018,(ridership_2013-ridership_2018)/ridership_2013*100 AS 5yr_change
from bigquery-public-data.new_york_subway.subway_ridership_2013_present;

I'm getting the result of:

division by zero: -3558 / 0

Where am I going wrong and how do I properly calculate percentage change? Thank you.

Solution

  • Just use the NULLIF function to replace the denominator to NULL when ridership_2013 is zero.

    SELECT
  station_name,
  ridership_2013,
  ridership_2018,
  (ridership_2013 - ridership_2018) / NULLIF(ridership_2013, 0) * 100 AS 5yr_change
FROM
  bigquery-public-data.new_york_subway.subway_ridership_2013_present;