'Oracle SQL Conditional SUM and TO_CHAR

I have a table with a sum that I want to format with $ at the beginning, but it gives me an error:

ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number.

select sum(TO_CHAR(a.freight, '$99,990.00')) AS "Costo total", (b.company_name) AS "Compañía fletera"
FROM orders a
inner join shippers b
on a.ship_via = b.shipper_id
group by b.company_name
order by b.company_name asc;

what I want is to see "Costo total" with a money format like $123.45

sql


Solution 1:[1]

Copied the comment as answer to make it possible to close the question: Your idea is ok, but you have to sum first and then convert to char, not vice versa. Meaning "SELECT TO_CHAR(SUM(a.freight),'$99,990.00')..."

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jonas Metzler

Related Questions