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

Hi Harald - Thanks for the idea ... I always forget about 'having'. Unfortunately the way I implemented I still got an empty MISC row if there were nothing which should have been in MISC. Still it was good to see your approach - thanks again.

richard.

Harald Maier <maierh_at_myself.com> wrote in message news:<m3ad9yrwph.fsf_at_ate.maierh>...
> richardshea_at_fastmail.fm (Richard Shea) writes:
>
> > 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
> >
>
> Why not use having?
>
> ,----[ Test ]
> | drop table score;
> | create table score (
> | id varchar2(10) constraint score_pk primary key,
> | val number(2)
> | );
> |
> | insert into score values ('A', 10);
> | insert into score values ('B', 21);
> | insert into score values ('C', 8);
> | insert into score values ('D', 22);
> | insert into score values ('E', 9);
> | insert into score values ('F', 98);
> |
> | select id, sum(val)
> | from score
> | group by id
> | having sum(val) > 20
> | union
> | select 'misc', sum(v)
> | from (
> | select id, sum(val) as v
> | from score
> | group by id
> | having sum(val) <=20
> | );
> `----
>
> ,----[ Result ]
> | ID SUM(VAL)
> | ---------- ----------
> | B 21
> | D 22
> | F 98
> | misc 27
> `----
>
> Harald
Received on Tue Aug 26 2003 - 07:53:05 CDT