Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

oracle query

Posted on 2013-05-14
9
297 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
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: 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
 
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

809 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