asked on

oracle query

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

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

MikeOM_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

thanks