SQL Gurus - Producing 'Misc' row only when necessary ?

From: Richard Shea <richardshea_at_fastmail.fm>
Date: 24 Aug 2003 23:24:27 -0700
Message-ID: <282f826a.0308242224.17d8423f@posting.google.com>

Hi - Anyone got any ideas on this. I have a query (prettied up, real version at end of post) ...

select * from tblValues

... the output looks like this ...

DESC VALUE
==== =====
A 10
B 21
C 8
D 22
E 9
F 98

... but how can I write a query which will group all rows with a value less then X into a 'Miscellanous' row ? That is so that the output
(for X = 20)would look like this ...

DESC VALUE
==== =====
B 21
D 22
F 98
MISC 27 ... ?

I *can* do this using a union and making "select * from tblValues" an inner query to a pair of (<X) UNION (>=X) type queries but the trouble is I then get the MISC row whether there are any qualifying rows or not (as I said the real version of this query is pasted at the bottom of this post - it's pretty ugly so I've tried to provide a simplifed version here).

I'm sure there's a smarter way of doing this altogether but anyone tell me what it is ?

Here goes with the real query - it's a work in progress so don't expect it to be pretty !

SELECT * FROM
(

SELECT
0 SORTCOLUMNONLY,
ABC_AUTOID,
ABC_DESCRIPTION,
SUM(DEF_DURATION_MINS) TOTMINS,

ROUND(AVG(DEF_DURATION_MINS),0) AVGMINSPERABC, 
ROUND(((SUM(DEF_DURATION_MINS)/1891)*100),1) PERCENTROUNDED, 

((SUM(DEF_DURATION_MINS)/1891)*100) PERCENTRAW
FROM
GHI_SAMPLE_PARTICIPANT,
VW_DEF_WITH_NAMES
where

DEF_PAR_ID = PAR_AUTOID AND 
PAR_GRV_ID = 5 AND 
DEF_START_DATETIME >= TO_DATE('19990101 00:00:00','YYYYMMDD

HH24:MI:SS') AND
DEF_END_DATETIME <= TO_DATE('20031231 23:59:59','YYYYMMDD HH24:MI:SS') GROUP BY ABC_AUTOID,ABC_DESCRIPTION
)
WHERE PERCENTRAW > 25
UNION
SELECT 1,-1,'Miscellaneous',SUM(TOTMINS),AVG(AVGMINSPERABC),SUM(PERCENTROUNDED),SUM(PERCENTRAW) FROM
(

SELECT
1 SORTCOLUMNONLY,
-1,
'Miscellaneous' ABC_DESCRIPTION,
SUM(DEF_DURATION_MINS) TOTMINS,

ROUND(AVG(DEF_DURATION_MINS),0) AVGMINSPERABC,
ROUND(((SUM(DEF_DURATION_MINS)/1891)*100),1) PERCENTROUNDED,

((SUM(DEF_DURATION_MINS)/1891)*100) PERCENTRAW
FROM
GHI_SAMPLE_PARTICIPANT,
VW_DEF_WITH_NAMES
where

DEF_PAR_ID = PAR_AUTOID AND
PAR_GRV_ID = 5 AND
DEF_START_DATETIME >= TO_DATE('19990101 00:00:00','YYYYMMDD

HH24:MI:SS') AND
DEF_END_DATETIME <= TO_DATE('20031231 23:59:59','YYYYMMDD HH24:MI:SS') GROUP BY ABC_AUTOID,ABC_DESCRIPTION
)
WHERE PERCENTRAW <= 25
ORDER BY 1,3
/ Received on Mon Aug 25 2003 - 01:24:27 CDT