Solved

oracle query

Posted on 2013-05-14
9
288 Views
Last Modified: 2013-05-15
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
0
Comment
Question by:anumoses
  • 4
  • 3
  • 2
9 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39166028
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
 
LVL 6

Author Comment

by:anumoses
ID: 39166053
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
 
LVL 40

Expert Comment

by:Sharath
ID: 39166140
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
 
LVL 6

Author Comment

by:anumoses
ID: 39166150
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:anumoses
ID: 39166164
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
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 39166170
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39167183
is there a way to (include several other items of data) but retain the exact same output
No
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39167186
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
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39169176
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now