Errors Inserting commas to separate spark sql query result

user_1592334 0
Viewed 71

I am trying to write some SQL queries on databricks that would produce outputs with commas. The code below successfully produced the output I wanted, but not with commas.

%sql
SELECT Country, CONCAT('£ ',TRIM(ROUND(SUM(Quantity * UnitPrice), 2))) as Country_sales
FROM ecommerce_new
GROUP BY Country
Order by SUM(Quantity * UnitPrice) Desc

Output

enter image description here

The idea is to produce something similar to the Country_sales column, where the values are separated with commas. I tried to use the TO_CHAR method with '9G999G999', but I kept getting errors.

Country           Country_sales
United Kingdom   £ 7,299,517.53
databricks apache-spark-sql
1 Answers

You need to use format_number function to do such formatting:

SELECT format_number(12332.123456, '£ ,###.-')
Alex_Ott_186270
Related