Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

oracle query

Posted on 2015-01-05
3
Medium Priority
?
117 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
[X]
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
3 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 1000 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 1000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

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 …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 how to recover a database from a user managed backup

618 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