Re: SQL query
{
>
> I have a simple table containing just one column.
> Example test data unloaded to ascii file could be:
>
> 001,001,002,002,002,003,004,005,006,006,007,007,007
>
> What I want to do is issue a select statement that groups together the
> 005, 006 and 007 as one such that I get the following output:
>
> 001,002, 003, 004, 999
>
> where 999 represents the others.
If I might speculate wildly for a moment, would it be the case that
somewhere you have data something like this:- }
create temp table mission ( agent_assigned char(3), mission_cost integer ) ;
insert into mission values ( "001", 50 ) ;
insert into mission values ( "001", 60 ) ;
insert into mission values ( "002", 20 ) ;
insert into mission values ( "002", 20 ) ;
insert into mission values ( "002", 30 ) ;
insert into mission values ( "003", 10 ) ;
insert into mission values ( "004", 50 ) ;
insert into mission values ( "005", 20 ) ;
insert into mission values ( "006", 10 ) ;
insert into mission values ( "006", 5 ) ;
insert into mission values ( "007", 5 ) ;
insert into mission values ( "007", 5 ) ;
insert into mission values ( "007", 7 ) ;
{ and you want a report that looks something like this:-
agent number_missions total_cost
----- --------------- ----------
001 2 110
002 3 70
003 1 10
004 1 50
999 6 52
5 row(s) retrieved.
Of so, this is how I would do it.... }
select unique agent_assigned, agent_assigned maps_to
from mission
into temp mapping with no log ;
update mapping
set maps_to = '999'
where maps_to in ( '005', '006', '007' ) ;
{ This update reflects the fact that:
> My example data was chosen to reflect how I knew that 001,002,003 and
> 004 were those I wanted to treat individually, and that 005, 006 and
> 007 were to be grouped together under the banner 'other'. }
select mapping.maps_to agent,
count(*) number_missions,
sum(mission.mission_cost) total_cost
from mission, mapping
where mission.agent_assigned = mapping.agent_assigned
group by 1
order by 1
{ - Paul (not a spokesman)
This program posts news to thousands of machines throughout the entire
civilized world, and parts of Australia. Your message will cost the net
hundreds if not thousands of dollars to send everywhere.
Damn good value if you ask me! }