0

I am now able to populate a new column with a calculated percentage with this SQL:

UPDATE <table>
SET <newColumn>=subtotal/total

Could you assist me with the required SQL query to set the populated result to 2 decimal places only?

I have tried:

SELECT round(perc_gr10_11,2) FROM ctn_youth_education

which gives me a syntax error, and I have also tried:

SELECT round( CAST(perc_gr10_11 as numeric),2) FROM ctn_youth_education

which gives me (obviously) a selection of the table with the rounding applied.

However, I would like the values in the column to reflect only 2 decimal places, not just a selection of the table. The purpose of this is for the pop-up labeling which looks weird with too many decimal places.

  • This SELECT round( CAST(perc_gr10_11 as numeric),2) FROM ctn_youth_education would work better if you just add all other columns into it: SELECT round( CAST(perc_gr10_11 as numeric),2) as newcolumn,* FROM ctn_youth_education . No need to create additional column and populate it then; unless your dataset is really big, then dynamic query would be slower. – JaakL Sep 20 '17 at 11:33
2

This solves the problem:

UPDATE t 
SET c = ROUND(CAST(c as NUMERIC), 2)

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.