1

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?
  • 1
    What Version of SQLite are you using? SQLite v3 supports UFT8/16 sqlite.org/version3.html – Mapperz Jun 5 '13 at 17:20
  • Yes, it's SQLite v3. That lead me to double check the data again in notepad. I discovered I made a mistake. Turns out the values are not numeric. See my post below. – user12711 Jun 5 '13 at 18:29
1

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.

  • You're right -- done. – user12711 Jul 9 '13 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.