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

From: Paul Dixon <root_at_127.0.0.1>
Date: Wed, 27 Aug 2003 12:17:08 +0000 (UTC)
Message-ID: <bii7g4$643$1@visp.bt.co.uk>

"Richard Shea" <richardshea_at_fastmail.fm> wrote in message news:282f826a.0308242224.17d8423f_at_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
Richard

depending on your Oracle version, you may be able to use a CASE statement e.g.

SELECT CASE WHEN val < 20 THEN 'MISC' ELSE ID END AS ID, SUM(val ) AS val FROM score
GROUP BY CASE WHEN val < 20 THEN 'MISC' ELSE ID END.

Paul Dixon Received on Wed Aug 27 2003 - 07:17:08 CDT