Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 2 of 2 total |
UNION & sorting |
Sun, Jul 7 2019 6:15 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, Jul 7 2019 6:30 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |