How can I capture Distinct values into a single GROUPED field? 2

[ljsmith91]

I am a novice sql reporter. I have a field called tape_media in a sql query that I am GROUPing up and counting how many unique tapes a job is writing as a summary for each day. I would like to also list each of these DISTINCT media in a new field that is grouped along with my other GROUP fields for each day but I have no idea if or how to do it. Along with this statement:

COUNT(DISTINCT tape_media) AS Total_Tape_Medias,

is there a way to append each of these DISTINCT tape_media entries to a single new GROUP field (ex. list_of_tape_media)with a "," separating each distinct tape media?

Thanks for any help.

ljs

 

[jimirvine]

Hi ljs
I'm a little unsure as to your exact requirements. If you post some sample data along with the results that you expect based on that sample data along with a text description / explanation of the logic behind that result set (may not be necessary if the data is self explanatory), it will be a lot easier to 'get' what your requirements are.

 

[ljsmith91]

jimirvine,

Thanks for replying. Here is sample sql table:

job jobid date tape_media

abc 121 2008-02-01 tp0001
bcd 141 2008-02-01 tp0003
abc 138 2008-02-01 tp0007
bcd 152 2008-02-01 tp0003
abc 172 2008-02-01 tp0007

I need output that would look like this:

date job total_tape_medias tape_medias_used

2008-02-01 abc 2 tp0001, tp0007
2008-02-01 bcd 1 tp0003

I want to get a count of the distinct tape_medias per job on each date and actually list them also in a new field that I create via the sql.

select date,job,count(distinct tape_media) as total_tape_medias, ?????
from my_table
group by date,job

Is it possible for me to list all the DISTINCT media in a new field in addition to simply counting the DISTINCT media?

Thanks so much for any help.

ljs

 

[jaxtell]

Hi ljs,
This is what you need to do.

http://www.tek-tips.com/viewthread.cfm?qid=1439224&page=1

 

[SantaMufasa]

LJS,

As I mentioned in Jaxtell's thread, above, a simple user-defined function can achieve what you want, as well:

select * from my_table;

JOB      JOBID JOB_DATE  TAPE_MEDIA
--- ---------- --------- -----------
abc        121 01-FEB-08 tp0001
bcd        141 01-FEB-08 tp0003
abc        138 01-FEB-08 tp0007
bcd        152 01-FEB-08 tp0003
abc        172 01-FEB-08 tp0007

5 rows selected.

create or replace function str_media (job_in varchar2, date_in date) return varchar2 is
    str_out varchar2(4000);
begin
    for x in (select distinct tape_media
                from my_table
               where job_in = job
                 and date_in = job_date) loop
        str_out := str_out||', '||x.tape_media;
    end loop;
    return ltrim(str_out,', ');
end;
/

Function created.

col tape_medias_used format a20
select job_date
      ,job
      ,count(distinct tape_media) as total_tape_medias
      ,str_media(job,job_date) as tape_medias_used
from my_table
group by job_date,job
/

JOB_DATE  JOB TOTAL_TAPE_MEDIAS TAPE_MEDIAS_USED
--------- --- ----------------- ----------------
01-FEB-08 abc                 2 tp0001, tp0007
01-FEB-08 bcd                 1 tp0003

2 rows selected.

Let us know your thoughts on this method, as well.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ljsmith91]

Hi all,

Thanks for the reply. However, I am not sure this will work for me. I am querying a proprietary oracle database where the vendor supplied the table views to us to allow querying outside of their application. So, I will be unable to create functions within their database. This is what I think you are suggesting. If I am not allowed to create is there another option ? Or am I wrong in what you are describing? Thanks so much.

ljs

 

[SantaMufasa]

LJS,

Despite your vendor's providing you with stand-alone, fully encapsulated database objects, they certainly cannot prevent you from creating your own schema named "ANCILLARY", for example, that has permission to create any objects (including user-defined functions) that can query any table or view in the proprietary portions of their product. (I agree, I would not create any objects in the proprietary schema(s) simply to avoid any controversy with the vendor over whether or not you have modified their product's schema.)

Your DBA can connect as the owner of the proprietary schema, then "GRANT SELECT..." privileges on the table(s) you need to access.

Let us know if you have follow-on questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jaxtell]

Hmm, I suppose Dave is right. As long as you only need to use it for this one particular SELECT (or others very similar), his solution is much simpler. My solution is more table/column/separator independent, so it might be more appropriate if you want the same functionality with different criteria.

 

[ljsmith91]

Thanks to you both. I will talk to my DBA and see if I can't get this moving forward. Great info. -ljs