How to sum a varchar column?

Question

I tried with the following code but get irrelevant values on display. I noticed that it is because of Varchar column for TX_AMOUNT. How to change the code to sum that varchar column?

String[] columns = {VivzHelper.UID, helper.TX_NAME, 
"SUM("+helper.TX_AMOUNT+") AS "+helper.TX_AMOUNT, helper.TX_DATE };

Cursor c = db.query(VivzHelper.TX_TABLE, columns, helper.TX_ID + "='" + name 
+ "' AND " + helper.TX_DATE + " BETWEEN '" + datefrom.from_date + "' AND '"  
+ dateto.to_date + "  ' ",null,helper.TX_NAME, null, null);

What do you mean by **summing a text** to another one? Do you want to `concatenate` them? — Phantômaxx, May 15 '15 at 12:18
The varchar column TX_AMOUNT holds numbers with decimals. I want to sum it. — user3144078, May 15 '15 at 12:24
Your code is hard to read. Use a string substitutor to build your query. See http://stackoverflow.com/a/10346795/1390015 — tbraun, May 15 '15 at 13:00
Why don't you use a **numeric** data type, intead of a **text** data type? So, you could sum, average and do any kind calculations on that field, without the need of **casting** to a numeric data type (which consumes CPU cycles)... This is why you have other data types, and not only text. — Phantômaxx, May 15 '15 at 14:10
If I use numeric will it accommodate big numbers like 5555555000.96 with decimals. — user3144078, May 15 '15 at 14:26
As said by you earlier I tried with a NUMERIC type column and when I wanted to get it to variable I got false numbers when the number in the column exceeds 6,2 (with decimal). To get the value in the column TX_AMOUNT (when in NUMERIC) I used like this: if (c != null) { c.moveToFirst(); while (c.isAfterLast() == false) { String txTotal=null; String v_uid=(c.getString(0));//tx_id is passed here..not used ofcourse txTotal = (c.getString(3)); //not ok Float fl=(c.getFloat(3)); //not ok How to get , the NUMERIC coulumn TX_AMOUNT to pass to a suitable variable for calculation purposes? — user3144078, May 16 '15 at 10:52

tbraun · Answer 1 · 2015-05-15T12:51:26.113

0

Use CAST

SELECT CAST('1.11' as decimal)

Source: http://www.sqlite.org/lang_expr.html#castexpr

Example:

CREATE TABLE Test
    (`id` int, `productName` varchar(7), `amount` varchar(10))
;

INSERT INTO Test
    (`id`, `productName`, `amount`)
VALUES
    (1, 'Foo', '1.1'),
    (2, 'Bar', '1.1'),
    (3, 'Baz', '1.1')
;

SELECT
  SUM(CAST(amount as decimal))
FROM
  Test
 ;

On your case:

String[] columns = {VivzHelper.UID, helper.TX_NAME, 
    "SUM(CAST("+helper.TX_AMOUNT+" as decimal)) AS "+helper.TX_AMOUNT, helper.TX_DATE };

edited May 15 '15 at 12:51

answered May 15 '15 at 12:18

tbraun

2,636
31
26

I am using string array with cursor query. So accordingly I need help in that angle. – user3144078 May 15 '15 at 12:42
@user3144078 try last using last block of code on my latest edit – tbraun May 15 '15 at 12:52
I have 4 rows with 2 diff TX_NAME . The data is hereunder: Row 1: Name=Krishna TX_AMOUNT is 555548350000.15 Row 2: Name=Krishna TX_AMOUNT is 1.14 Row 3: NAme=Tea TX_AMOUNT is 52.0 Row 4:Name=Tea TX_AMOUNT is 214.0 – user3144078 May 15 '15 at 13:17
@user3144078 I believe your query is wrong. If you have 10 rows and want to sum the value of a column you won't be able to get the other values without GROUPING them. You'll need another query or subquery. See http://www.techonthenet.com/sqlite/functions/sum.php – tbraun May 15 '15 at 13:24
I have 4 rows with 2 diff TX_NAME . The data is hereunder: Row 1: Name=Krishna TX_AMOUNT is 555548350000.15 Row 2: Name=Krishna TX_AMOUNT is 1.14 Row 3: NAme=Tea TX_AMOUNT is 52.0 Row 4:Name=Tea TX_AMOUNT is 214.0 The grand total is 5554835267.29 which I get when I run another code without using 'SUM'. Now, here my requirement is I want to consolidate the names: That is out of 4 rows 2 belongs to krishna and two belongs to Tea. I want the SUM of the 'names alone' . In the code you gave it gives me the grand total for 4 rows as 5554840266. Earlier I used BigDecimal to get correct total. – user3144078 May 15 '15 at 13:27
In my previous comment the 1st Row amount is 5554835000.15. The same code works well if the column type is not varchar. In that case the result I get is some junk for large sized amounts. So, when testing with large size amount like 5554835000.15 I had to use Varchar. – user3144078 May 15 '15 at 13:37
@user3144078 Again, your query is wrong and I don't understand what you are trying to achieve. What's the result you're expecting from this query? You're trying to get ID | NAME | AMOUNT | DATE . If you want to sum the AMOUNT rows, you'll need to discard the other columns or group them. But it doesn't make sense grouping ID, so you'll need to rethink your query. But my response is CORRECT for your question "How to sum a varchar column?". – tbraun May 15 '15 at 14:37
@user3144078 This response ANSWERS the question "How to sum a varchar column?" – tbraun May 15 '15 at 14:38

How to sum a varchar column?

1 Answers1