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

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 Mon Aug 25 2003 - 03:02:50 CDT