wisseman78 wisseman78 - 1 year ago 102
iOS Question

Taking Average of Money in SQL/SQLPro

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';

Is there something wrong with the code I am using, because I think it's right.

Answer Source

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))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download