Android Question SELECT 'Total' as Total, SUM( MyValue ) FROM...

DeerBear

Member
Licensed User
Hello!

I'd like to do what is written in the subject in a SQLite query.
Does anybody know of a way to do it? I've googled "android how to return a fixed string in a sqlite query" but I can't see any results (not even SO) about that... is it possible nobody's needed this?

How do you solve this kind of issue?

Kind Regards,

A
 

sorex

Expert
Licensed User
other database have a CAST command to get the job done, not sure if SQLite has this.
(especially usefull when concatenating a string with number, not requires for just text)

CAST('Total' as varchar(5)) as Total
 
Upvote 0

DeerBear

Member
Licensed User
other database have a CAST command to get the job done, not sure if SQLite has this.
(especially usefull when concatenating a string with number, not requires for just text)

CAST('Total' as varchar(5)) as Total

Many also just allow the syntax shown in the subject :)

A
 
Upvote 0

Mahares

Expert
Licensed User
Are you looking for something like this where you assign your expression to a variable and include it in the query:
B4X:
Dim strVar As String="TOTAL"
txt="SELECT '" & strVar & "' AS DeerBear ,SUM(CODE) as SCODE FROM tblChemicals GROUP BY RANK "
Cursor1= SQL1.ExecQuery(txt)
Cursor1.position=8
Log(Cursor1.GetString("DeerBear") & "  " &  Cursor1.GetString("SCODE"))
 
Upvote 0

sorex

Expert
Licensed User
no, it's the select 'xxx' as fieldname that doesn't seem to work.

Edit: I re-read the initial post and he doesn't even say that it didn't work, you never know when you don't try it I guess.
 
Upvote 0

udg

Expert
Licensed User
Hi all,
I don't use sqlite but what about something like the following?
B4X:
DECLARE @MyTotal NCHAR(5);
SET @MyTotal = 'Total';
SELECT @MyTotal, SUM(MyValue) FROM...

And a brief look at Sqlite doc showed a quote(X) function which should return X literal or even coalesce(X,Y,.) function that returns first not null parameter. Hope some of the above may help you.

Umberto
 
Upvote 0

sorex

Expert
Licensed User
no, it seems he wants the first field to output 'total'.

it doesn't make much sense actually, if you know that field is always 'Total' I don't see a reason to add it to the restult
as you know the result in advance.
 
Upvote 0

udg

Expert
Licensed User
Hi,

testing on http://sqlfiddle.com with
B4X:
SELECT 'Total', * FROM MYTABLE;
The output was:
'Total' ID, NAME
Total 1, Joe
Total 2, John

While with code similar to yours, I have
B4X:
SELECT 'Total', SUM(ID) FROM MYTABLE;
returns:
'Total' SUM(ID)
Total 3

Regards,

Umberto
 
Upvote 0

DeerBear

Member
Licensed User
no, it seems he wants the first field to output 'total'.

it doesn't make much sense actually, if you know that field is always 'Total' I don't see a reason to add it to the restult
as you know the result in advance.

Hi!

It does make sense if you want to take advantage of ExecuteListView and have a 2 lines items where the first line says "Total" and the second shows you that very total :)
 
Upvote 0
Top