[GIS] Why doesn’t SQL sum(dollar) work in SQLITE

guiqspatialitespatialitesqlite

I've got a sqlite table with real numbers, two decimal place precision, in a mydollar_column. (There are no null values, but some of the values are negative numbers) For some reason, using sql on those values, always returns 0.00000. I've tried this in Qspatialite (with QGIS 1.8) and Spatialite_GUI.

Here's an SQL example: SELECT sum(mydollar_column) FROM myTable

If I use the sum function on an integer column, it works. For example, I used sum(pkuid)on the primary key, and the sum() works as expected.

As a test, when I export the table to csv file, the values of my dollar_column are real numbers with two decimal place precision, for example "1234.56" But Qspatialite and Spatialite GUI always shows the values as, for example "$1,234.56" I've also used the total(mydollar_column) function with the same unsuccessful results.

  1. Does anyone know why I'm unable to calculate my dollar_column?
  2. How does Qspatialite and Spatialite_GUI interpret the values of mydollar_column as dollars, when sqlite is typeless columns?

Best Answer

It turns out that mydollar_column is a string of non numeric characters with commas and a dollar sign. I had been checking the csv version in Excel. Excel displays the values of those strings, within the formula bar, as you click on each cell, as all numeric values (no dollar signs or commas displayed in the data) i.e. "1234.56" when the cells value was actually "$1,234.56". And I was thinking that the values in the Excel rows showing the true values, i.e. "$1,234.56" was just a user friendly rendering of the data that it typed out automatically, and that Qspatialite and Spatialite_GUI were somehow doing the same thing, from true decimal numbers. I checked the csv in notepad and discovered this.

SQL sum() can't calculate the non numerica data. I will parse the field into numbers instead.

Maybe this thread will be helpful to someone in the future.

Related Question