sqlite union and labels

[sandy771]

I have the following query which works OK

SELECT cat, COUNT(*), COUNT(tag) FROM rtable WHERE qu > 0 AND qu < 4 GROUP BY qu

An example table would look something like

Code: Select all

 1     54     3
 2     26     4
 3     56     8

I want to modify the above sql query to sum the second and third columns and get a resultant table something like

Code: Select all

 1     54     3
 2     26     4
 3     56     8
 tot   136    15

SELECT cat, COUNT(*), COUNT(tag) FROM rtable WHERE qu > 0 AND qu < 4 GROUP BY qu
UNION ALL
SELECT 'tot', COUNT(*), COUNT(tag)
FROM table
WHERE qu > 0 and qu < 4

If I run the query at an sqlite command prompt the resultant table is correct

If I run the same query through a TUniQuery I dont get the 'tot' label but rather I get

Code: Select all

 1     54     3
 2     26     4
 3     56     8
 0     136    15

[CristianP]

Hallo,

If the "cat" field from "rtable" is numeric you must cast "cat" field to some string type.

Code: Select all

  UniQuery1.DataTypeMap.AddFieldNameRule('cat', ftString, 20);

And change a little you query to be more clear:
SELECT cat, COUNT(*), COUNT(tag) FROM rtable WHERE qu > 0 AND qu < 4 GROUP BY qu
UNION ALL
SELECT 'tot' AS cat, COUNT(*), COUNT(tag)
FROM table
WHERE qu > 0 and qu < 4

Or cast directly from query:
SELECT CAST(cat AS TEXT), COUNT(*), COUNT(tag) FROM rtable WHERE qu > 0 AND qu < 4 GROUP BY qu
UNION ALL
SELECT 'tot', COUNT(*), COUNT(tag)
FROM table
WHERE qu > 0 and qu < 4

Best Regards,
Cristian Peţa

[AlexP]

Hello,

sandy771

In Delphi, a field cannot contain records of different types, and the column type is defined by the first line, therefore you get 0 when attempting to map text to integer. You should use either the CAST mapping function or the DataTypeMapping technology, as it was suggested above.

[sandy771]

Thanks