Solved

oracle query

Posted on 2015-01-05
3
109 Views
Last Modified: 2015-01-06
CREATE TABLE TAB1
(
  DRIVE_DATE      DATE                          NOT NULL,
  AREA_REP_NO     NUMBER(4)                     NOT NULL,
  PROCEDURE_CODE  VARCHAR2(5 BYTE)
)




Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'DR');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 15, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 15, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 15, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 15, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'DR');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'DR');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 12, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 12, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 12, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'DR');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
Insert into TAB1
   (DRIVE_DATE, AREA_REP_NO, PROCEDURE_CODE)
 Values
   (TO_DATE('01/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10, 'WB');
COMMIT;

Open in new window


select drive_date,area_rep_no,
       procedure_code,count(*) as cnt
  from tab1
 group by drive_date,area_rep_no,
       procedure_code 

Open in new window


Need help in the count

If procedure_code is DR (double red)  then I need count multiplied by 2. For WB (whole blood) its 1
0
Comment
Question by:anumoses
3 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 40531733
I didn't test it, but this should do it:

SELECT drive_date, 
       area_rep_no, 
       procedure_code, 
       SUM(CASE 
             WHEN procedure_code = 'DR' THEN 2 
             ELSE 1 
           END) AS cnt 
FROM   tab1 
GROUP  BY drive_date, 
          area_rep_no, 
          procedure_code 

Open in new window

0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 250 total points
ID: 40531742
This should work!
  SELECT Drive_Date
       , Area_Rep_No
       , Procedure_Code
       , DECODE ( Procedure_Code, 'DR', 2, 1 ) * COUNT ( * ) AS Cnt
    FROM Tab1
GROUP BY Drive_Date, Area_Rep_No, Procedure_Code
/

Open in new window

0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40533313
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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

910 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