Manipulating columns BigQuery: divide two columns then round and covert to % or currency

Question

I'm trying to understand how to work with columns and every time I used more than one formula I get an error (clearly used to excel)

From my understanding i need to to use safe_divide as the iferror equivalent so I've got this:

concat('£', safe_divide(cost,clicks)) as CPC,

When I add round I get an error, so wondering what's the best way to manipulate columns?

concat('£', safe_divide(round(cost,clicks,2))) as CPC,

This will be super simple but I've had a quick google and not found anything so please direct me to a good resource / docs as the Google Big query mathematical functions documentation hasn't helped (https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#round)

I was about to post this and realised that it's obviously not going to work because adding the £ will converts it to string so looked at the conversion functions documentation and it looks like the only way (although I'm sure it's just me being a noob, unless the best way is to create a new table?) Also, please remember i'm new to Bigquery so take it easy on me please haha

Thanks!

Answer 1

You can only ROUND() a single number:
https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#round

So you would first do the SAFE_DIVIDE() and then do the ROUND().
Here's an example that you can test:

SELECT CONCAT('£', ROUND(SAFE_DIVIDE(3.0, 5), 2));

You have to realize though that if you divide by NULL or 0.0, the resulting value of SAFE_DIVIDE() will also be NULL.