How can i typecast a varchar to float for using aggregate sum() function with group by

I have a table called Fare:

CREATE TABLE Fare
(
     Date DATE NOT NULL,
     CardFare VARCHAR(5) NOT NULL,
     FullFare VARCHAR(5) NOT NULL
)

I have to group by date and sum cardfare and fullfare but they are of type varchar and have $ sign in front of them.

This is the result from running: Select * from Fare

Answer

Try below – using replace() and cast()

select 
    date,
    sum(cast(replace(cardfare,'$','') as decimal(16,2))) as totalcardfare,
    sum(cast(replace(fullfare,'$','') as decimal(16,2))) as totalfullfare
from fare
group by date