oracle query

select CASE
            WHEN coach_drive='C' THEN 'Coach'
            WHEN coach_drive='M' THEN 'Mini'
            ELSE 'Inside'
          END coach_drive,
          sum(a.projection) tot_projection,
          sum(nvl(a.actual_draw,0) ) tot_actual_draw
  from blood_drives a,
       sites b
where a.site_code = b.site_code
  and a.projection != 0
  and a.drive_cancelled is null
  and a.drive_date between '01-apr-2013' and '30-apr-2013'
group by coach_drive,a.audit_key,drive_date

COACH_DRIVE|TOT_PROJECTION|TOT_ACTUAL_DRAW
Coach|1543|1261
Mini|353|273
Inside|5329|4584

If I want to add the drive_id and audit_key then I will get more lines of data.
Is there a way to avoid it, but still use in the query but only get the data like above?

select CASE
            WHEN coach_drive='C' THEN 'Coach'
            WHEN coach_drive='M' THEN 'Mini'
            ELSE 'Inside'
          END coach_drive,
          sum(a.projection) tot_projection,
          sum(nvl(a.actual_draw,0) ) tot_actual_draw,
          a.audit_key.a.drive_id
  from blood_drives a,
       sites b
where a.site_code = b.site_code
  and a.projection != 0
  and a.drive_cancelled is null
  and a.drive_date between '01-apr-2013' and '30-apr-2013'
group by coach_drive,a.audit_key,drive_date
LVL 6
anumosesAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
I don't know your requirement. Check this if it helps you.
select CASE
            WHEN coach_drive='C' THEN 'Coach'
            WHEN coach_drive='M' THEN 'Mini'
            ELSE 'Inside'
          END coach_drive, 
          sum(a.projection) tot_projection,
          sum(nvl(a.actual_draw,0) ) tot_actual_draw,
          max(a.audit_key) audit_key,
		  max(a.drive_id) drive_id
  from blood_drives a,
       sites b
where a.site_code = b.site_code
  and a.projection != 0 
  and a.drive_cancelled is null
  and a.drive_date between '01-apr-2013' and '30-apr-2013'
group by coach_drive

Open in new window

0
 
SharathData EngineerCommented:
If you just want the data as you have right now and add additional columns, you need to do aggregation on those columns?
Do you want the max audit_key/drive_id?  Can you post some expected result?
0
 
anumosesAuthor Commented:
COACH_DRIVE|TOT_PROJECTION|TOT_ACTUAL_DRAW|DRIVE_DATE|AUDIT_KEY
Coach|21|17|4/1/2013|167544
Mini|11|7|4/1/2013|167618
Mini|12|13|4/1/2013|170063
Inside|25|7|4/1/2013|166278
Inside|25|15|4/1/2013|167315
Inside|31|24|4/1/2013|167561
Coach|50|14|4/2/2013|167587
Coach|12|12|4/2/2013|167614
Coach|46|32|4/2/2013|167629
Mini|9|6|4/2/2013|167419
Coach|34|32|4/3/2013|167332
Coach|15|8|4/3/2013|167569
Coach|30|35|4/3/2013|167570
Coach|16|14|4/3/2013|167571
Coach|12|8|4/3/2013|170099
Mini|13|12|4/3/2013|167572
Inside|25|18|4/3/2013|167287
Inside|31|26|4/3/2013|167299
Inside|24|12|4/3/2013|167568
Inside|45|33|4/3/2013|168345


But expected is

COACH_DRIVE|TOT_PROJECTION|TOT_ACTUAL_DRAW
Coach|1543|1261
Mini|353|273
Inside|5329|4584
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
SharathData EngineerCommented:
I think you already have the expected result. Do you want DRIVE_DATE|AUDIT_KEY in the result set? If yes, which value for these columns? max/min??
0
 
anumosesAuthor Commented:
I do not want any values to be displayed. The reason is I have to write another function in na report based on this field. Thats the reason I want the drive_date and audit key. But if I choose then I dont get sum of projection and actual_drive for the coach, mini and inside.
0
 
anumosesAuthor Commented:
select new_value
   into v_new
 from blood_drives_audit
where audit_key = :audit_key
       and column_name = 'projection'
       and audit_insert_date <= (:drive_date - :p_days1)
       and audit_insert_date = (select max(audit_insert_date)
                                  from blood_drives_audit cc
                                      where cc.audit_key = :audit_key
                                               and cc.column_name = 'projection');

If I choose the drive_date and audit key, I want to use them in this query. Is there a way to add this query in the first one?
0
 
PortletPaulfreelancerCommented:
is there a way to (include several other items of data) but retain the exact same output
No
0
 
PortletPaulfreelancerCommented:
Coach|12|12|4/2/2013|167614
Coach|12|8|4/3/2013|170099
Coach|15|8|4/3/2013|167569
Coach|16|14|4/3/2013|167571
Coach|21|17|4/1/2013|167544
Coach|30|35|4/3/2013|167570
Coach|34|32|4/3/2013|167332
Coach|50|14|4/2/2013|167587
Coach|46|32|4/2/2013|167629 ?

Coach|1543|1261

Inside|24|12|4/3/2013|167568
Inside|25|15|4/1/2013|167315
Inside|25|18|4/3/2013|167287
Inside|25|7|4/1/2013|166278
Inside|31|24|4/1/2013|167561
Inside|31|26|4/3/2013|167299
Inside|45|33|4/3/2013|168345 ?

Inside|5329|4584

Mini|11|7|4/1/2013|167618
Mini|12|13|4/1/2013|170063
Mini|9|6|4/2/2013|167419
Mini|13|12|4/3/2013|167572 ?

Mini|353|273

which items of extra data would be required, the italic ones?
(italics applied to the 'most recent date, and highest audit id')
0
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.