Devart Forums

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
I want to modify the above sql query to sum the second and third columns and get a resultant table something like
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

Hallo,

If the "cat" field from "rtable" is numeric you must cast "cat" field to some string type. 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

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.

Thanks