I'm trying to produce a result set that has a total. The SQL

SELECT _ID AS Asset, _Item AS Item, _Location AS Location, _Book AS "Book Value" FROM Assets WHERE _Disposed IS NULL
UNION
SELECT CAST(NULL AS integer),CAST(null AS VARCHAR),'TOTAL BOOK VALUE',SUM(_Book) FROM Assets

almost works. The part that doesn't work is that the result set is sorted and the total row goes to the top.

Is this WAD or a bug or a feature?


Roy Lambert

Found a workround

SELECT * FROM
(
SELECT CAST(_ID AS VARCHAR(5)) AS Asset, _Item AS Item, _Location AS Location, _Book AS "Book Value" FROM Assets WHERE _Disposed IS NULL
UNION
SELECT #160,'','',CAST(NULL AS DECIMAL(19,2)) FROM Assets
UNION
SELECT #160,'','TOTAL BOOK VALUE',SUM(_Book) FROM Assets
) Interim
ORDER BY Asset


#160 is a "hard" space so it doesn't show

Roy Lambert