Re: Query to show actual value if count=1

Tom,

Replace your 'One' with TO_CHAR(MAX(jobid). Same for location

SELECT empid,

         DECODE(COUNT (DISTINCT jobid), 1, TO_CHAR(MAX(jobid)), 'Many') jobs,

         DECODE(COUNT (DISTINCT location), 1, TO_CHAR(MAX(location)), 'Many') locations
FROM empjobs
GROUP BY empid

HTH,
Ranga Chakravarthi

"Tom Barnes" <barnest_at_san.rr.com> wrote in message news:ae6b6116.0211141107.67b634a4_at_posting.google.com...
> We have a table that tracks the tasks employees work on every day and
> also the employees' locations. We need a query that summarizes for a
> certain time period the tasks and locations for each employee. If an
> employee worked on multiple tasks/locations we want the query to say
> 'Many', otherwise it should display the task/location.
>
> CREATE TABLE empjobs (empid NUMBER, day DATE, jobid NUMBER, location
> NUMBER);
>
> INSERT INTO empjobs VALUES (1,TRUNC(SYSDATE),100,1000);
> INSERT INTO empjobs VALUES (1,TRUNC(SYSDATE-1),200,1000);
> INSERT INTO empjobs VALUES (1,TRUNC(SYSDATE-2),200,2000);
> INSERT INTO empjobs VALUES (2,TRUNC(SYSDATE),100,1000);
> INSERT INTO empjobs VALUES (2,TRUNC(SYSDATE-1),200,1000);
> INSERT INTO empjobs VALUES (2,TRUNC(SYSDATE-2),200,1000);
> INSERT INTO empjobs VALUES (3,TRUNC(SYSDATE),100,1000);
> INSERT INTO empjobs VALUES (3,TRUNC(SYSDATE-1),100,1000);
> INSERT INTO empjobs VALUES (3,TRUNC(SYSDATE-2),100,1000);
>
> Desired result:
>
> EMPID JOBID LOCATION
> ----- ----- --------
> 1 Many Many
> 2 Many 1000
> 3 100 1000
>
> The closest I can get is using a query like this:
>
> SELECT empid,
> DECODE(COUNT(DISTINCT jobid),1,'One','Many') jobid,
> DECODE(COUNT(DISTINCT location),1,'One','Many') location
> FROM empjobs
> WHERE day BETWEEN SYSDATE-30 AND SYSDATE
> GROUP BY empid;
>
> Which gives me the following result:
>
> EMPID JOBID LOCATION
> ----- ----- --------
> 1 Many Many
> 2 Many One
> 3 One One
>
> Oracle8i Enterprise Edition Release 8.1.6.1.0
>
> Thanks a lot,
>
> -Tom
>
Received on Thu Nov 14 2002 - 21:25:08 CST