How do I return a value to 2 decimal places from my SQL calculation?-CodePudding

I have the follow sql statement and i want to return the value in "percent_change" to 2 decimal places

SELECT
  station_name,
  routes,
  ridership_2013,
  ridership_2014,
  ridership_2014 - ridership_2013 AS change_2013_raw,
  ((ridership_2014 - ridership_2013)/ ridership_2013)*100 AS Percent_Change,
FROM
  bigquery-public-data.new_york_subway.subway_ridership_2013_present
WHERE
  ridership_2013 <>0 OR ridership_2014 <>0

I do not know when to add my roundup statement.

CodePudding user response：

Should be something like this:

SELECT
  station_name,
  routes,
  ridership_2013,
  ridership_2014,
  ridership_2014 - ridership_2013 AS change_2013_raw,
  ROUND(((ridership_2014 - ridership_2013)/ ridership_2013)*100, 2) AS Percent_Change,
FROM
  bigquery-public-data.new_york_subway.subway_ridership_2013_present
WHERE
  ridership_2013 <>0 OR ridership_2014 <>0

CodePudding user response：

If you want to round the value in "percent_change", than you need to include your whole calculation in the ROUND() function.

For your calculation this will look as follows:

ROUND(((ridership_2014 - ridership_2013)/ ridership_2013) * 100, 2) AS Percent_Change

So in your query that looks like this:

SELECT station_name
, routes
, ridership_2013
, ridership_2014
, ridership_2014 - ridership_2013 AS change_2013_raw
, ROUND(((ridership_2014 - ridership_2013)/ ridership_2013) * 100, 2) AS Percent_Change
FROM bigquery-public-data.new_york_subway.subway_ridership_2013_present 
WHERE ridership_2013 <> 0 
OR ridership_2014 <> 0

For more information on the ROUND() function, see https://www.w3schools.com/sql/func_sqlserver_round.asp