I am working on a dataset, and I am having trouble trying to calculate the average of a column with dollar currencies in it. All the values, for example, are listed in the format of $12.00, $5.43, $1,234.00.....
Whenever I use the code below, it returns a value of 0 as the avg. In the same vein, it does the same thing for the SUM command as well.
SELECT AVG(bill_amount) FROM Trips WHERE trip_date >= '08/13/13';
You would appear to be storing values in the
bill_amount column as text rather than as a number. You can convert them to a number and then use average. Something like this:
select avg(cast(case when substr(bill_amount, 1, 1) between '0' and '9' then bill_amount else substr(bill_amount, 2) end) as decimal(18, 4))