CODEWITHSUNDEEP
sqlsqlitesum
Jacob Wood
Jacob Wood

Reputation: 467

sqlite query to find sum of all values in a column

This column is the price in sqlite.I am using SELECT SUM(price) FROM example; But the value is not correct its just 5 digit number followed by 67 number and 6 zeros and 1, eg: 34,456.670000001 . The column is created as a VARCHAR .I have tried numeric(100) and Integer but the value still remains the same .Please guide me through as I am a noob in db.Thanks again.I would really appreciate the help.

345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
126,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
75,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
52,334,123.32 
2,442,887.21
255,165.11 
3,889.02 
622.43 
916.52 
42.22 
17,234.01 
77,245.67 
104,245.00 
332.98 
6,875.21 
12,225.14 
52.54 
402.82 
32,875.32 
160,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.00 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.32 
2,442,887.21
255,165.11 
3,889.02 
622.43 
916.52 
42.22 
17,234.01 
77,245.67 
104,245.00 
332.98 
6,875.21 
12,225.14 
52.54 
402.82 
32,875.32 
246,323,743.32 
345,887.45
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,237,129.57 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.32 
2,442,887.21
255,165.11 
3,889.02 
622.43 
916.52 
42.22 
17,234.01 
77,245.67 
104,245.00 
332.98 
6,875.21 
12,225.14 
52.54 
402.82 
32,875.32 
183,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,322,555.39 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.32 
2,442,887.21
255,165.11 
3,889.02 
622.43 
916.52 
42.22 
17,234.01 
77,245.67 
104,245.00 
332.98 
6,875.21 
12,225.14 
52.54 
402.82 
32,875.32 
153,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,400,321.23 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.32 
2,442,887.21
255,165.11 
3,889.02 
622.43 
916.52 
42.22 
17,234.01 
77,245.67 
104,245.00 
332.98 
6,875.21 
12,225.14 
52.54 
402.82 
32,875.32 
83,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,341,152.99 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
139,334,123.32 
2,442,887.21
255,165.11 
3,889.02 
622.43 
916.52 
42.22 
17,234.01 
77,245.67 
104,245.00 
332.98 
6,875.21 
12,225.14 
52.54 
402.82 
32,875.32 
164,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,299,477.81 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
139,334,123.32 
2,442,887.21
255,165.11 
3,889.02 
622.43 
916.52 
42.22 
17,234.01 
77,245.67 
104,245.00 
332.98 
6,875.21 
12,225.14 
52.54 
402.82 
32,875.32 
166,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,309,926.11 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
139,334,123.32 
2,442,887.21
255,165.11 
3,889.02 
622.43 
916.52 
42.22 
17,234.01 
77,245.67 
104,245.00 
332.98 
6,875.21 
12,225.14 
52.54 
402.82 
32,875.32 
83,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,334,123.32 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
129,387,002.55 
345,887.21
16,520.11 
388,902.02 
24,622.43 
278,916.52 
42.22 
17,234.01 
377,245.67 
5,104,245.00 
23,332.98 
126,875.21 
5,112,225.14 
4,552.54 
402.82 
132,875.32 
31,334,123.32 
2,442,887.21
255,165.11 
3,889.02 
622.43 
916.52 
42.22 
17,234.01 
77,245.67 
104,245.00 
332.98 
6,875.21 
12,225.14 
52.54 
402.82 
32,875.32 
83,334,123.32

Upvotes: 0

Views: 900

Answers (1)

mu is too short
mu is too short

Reputation: 434665

The problem is that SQLite will truncate the numbers at the commas when it implicitly casts those strings to numbers so that it can SUM them. For example:

sqlite> select '1,000' + '2,000';
'1,000' + '2,000'
3

All you need to do is strip out the commas. You should be able to use replace and then let SQLite's default type conversions take it from there:

SELECT SUM(replace(price, ',', '')) FROM example

For example:

sqlite> select replace('1,000', ',', '') + replace('2,000', ',', '');
replace('1,000', ',', '') + replace('2,000', ',', '')
3000

Upvotes: 3

Related Questions