Home » SQL & PL/SQL » SQL & PL/SQL » query datewise howmany records (oracle 12c)
query datewise howmany records [message #666493] Thu, 09 November 2017 00:23 Go to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
I have two tables and inserted data as below

create table processed_requests(recseq number,id number,farmid number,created_date date,isvalid number,result number);
/

alter table processed_requests add constraint pl_pk promary key (recseq,id,farmid);
/

create table matched_records(recseq number,id number,farmid number,type number);
/

alter table matched_records add constraint fk_mr foreign key (recseq,id,farmid) reference processed_requests(recseq,id,farmid);
/
insert into processed_requests values(1,1,2,sysdate,1,111);
insert into processed_requests values(2,1,3,sysdate-1,1,103);
insert into processed_requests values(3,1,4,sysdate,1,111);
insert into processed_requests values(4,1,5,sysdate-1,1,103);
insert into processed_requests values(5,1,6,sysdate,1,111);
insert into processed_requests values(6,1,7,sysdate,1,103);

insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,2,26);

insert into matched_records values (1,1,3,26);
insert into matched_records values (1,1,3,26);
insert into matched_records values (1,1,3,56);
insert into matched_records values (1,1,4,26);
insert into matched_records values (1,1,4,39);
insert into matched_records values (1,1,2,26);
insert into matched_records values (1,1,5,39);
insert into matched_records values (1,1,5,26);
insert into matched_records values (1,1,6,38);
insert into matched_records values (1,1,7,28);

commit;


I need query datewise howmany records are created and out of which how many only type=26 records are present and their percentage along with recseq?
output should contain the below columns.
Please any one help me



date | recseq| total_records|only_type_26


Note:I have million of records in both tables and query should execute very fast.

Regards,
Rajesh
Re: query datewise howmany records [message #666496 is a reply to message #666493] Thu, 09 November 2017 02:23 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
Please try the following
-- preparing data
WITH
    PROCESSED_REQUESTS(RECSEQ, ID, FARMID, CREATED_DATE, ISVALID, RESULT)
    AS
        (SELECT 1, 1, 2, SYSDATE, 1, 111 FROM DUAL
         UNION ALL SELECT 2 ,1 ,3 ,SYSDATE - 1 ,1 ,103 FROM DUAL
         UNION ALL SELECT 3, 1, 4, SYSDATE, 1, 111 FROM DUAL
         UNION ALL SELECT 4 ,1 ,5 ,SYSDATE - 1 ,1 ,103 FROM DUAL
         UNION ALL SELECT 5, 1, 6, SYSDATE, 1, 111 FROM DUAL
         UNION ALL SELECT 6, 1, 7, SYSDATE, 1, 103 FROM DUAL),
    MATCHED_RECORDS(RECSEQ, ID, FARMID, TYPE)
    AS
        (SELECT 1, 1, 2, 26 FROM DUAL
         UNION ALL SELECT 1, 1, 2, 26 FROM DUAL
         UNION ALL SELECT 1, 1, 2, 26 FROM DUAL
         UNION ALL SELECT 1, 1, 2, 26 FROM DUAL
         UNION ALL SELECT 1, 1, 2, 26 FROM DUAL
         UNION ALL SELECT 1, 1, 2, 26 FROM DUAL
--         UNION ALL SELECT 1, 1, 2, 25 FROM DUAL
         UNION ALL SELECT 1, 1, 3, 26 FROM DUAL
         UNION ALL SELECT 1, 1, 3, 26 FROM DUAL
         UNION ALL SELECT 1, 1, 3, 56 FROM DUAL
         UNION ALL SELECT 1, 1, 4, 26 FROM DUAL
         UNION ALL SELECT 1, 1, 4, 39 FROM DUAL
         UNION ALL SELECT 1, 1, 2, 26 FROM DUAL
         UNION ALL SELECT 1, 1, 5, 39 FROM DUAL
         UNION ALL SELECT 1, 1, 5, 26 FROM DUAL
         UNION ALL SELECT 1, 1, 6, 38 FROM DUAL
         UNION ALL SELECT 1, 1, 7, 28 FROM DUAL)
-- this is your query
SELECT   PROCESSED_REQUESTS.CREATED_DATE
        ,MATCHED_RECORDS.RECSEQ
        ,SUM(PROCESSED_REQUESTS.RESULT)                                                                          AS TOTAL_RECORDS
        ,SUM(CASE WHEN TYPE = 26 THEN PROCESSED_REQUESTS.RESULT ELSE 0 END)                                      AS ONLY_TYPE_26
        ,SUM(CASE WHEN TYPE = 26 THEN PROCESSED_REQUESTS.RESULT ELSE 0 END) / SUM(PROCESSED_REQUESTS.RESULT) * 100 AS PERCENTAGE
    FROM PROCESSED_REQUESTS
         FULL OUTER JOIN MATCHED_RECORDS
             ON (PROCESSED_REQUESTS.RECSEQ = MATCHED_RECORDS.RECSEQ
             AND PROCESSED_REQUESTS.ID = MATCHED_RECORDS.ID
             AND PROCESSED_REQUESTS.FARMID = MATCHED_RECORDS.FARMID)
GROUP BY PROCESSED_REQUESTS.CREATED_DATE, MATCHED_RECORDS.RECSEQ
ORDER BY PROCESSED_REQUESTS.CREATED_DATE, MATCHED_RECORDS.RECSEQ
To show what overhead your example data produce I've used a FULL OUTER JOIN. Please use a normal JOIN to see only "valid data".

I've inserted an additional line to show that the percentage calculation works. Please remove the comment ('--') in the data preparation part to enable it.

[Updated on: Thu, 09 November 2017 02:35]

Report message to a moderator

Re: query datewise howmany records [message #666498 is a reply to message #666496] Thu, 09 November 2017 02:55 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks quirks for your quick reply.
Sorry i have little change in output ..i am expecting result as below

date:date of the record
recseq:distinct recseq
total_records:total records for the single date
only_type_26:for the single day how many records having only 26 type it should not contain other type for the same recseq.
so my output should come as below

date| recseq| total_records|only_type_26
sysdate|1|4|1
Re: query datewise howmany records [message #666499 is a reply to message #666498] Thu, 09 November 2017 03:04 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
To my understanding, this is exactly what I gave you. Please be more descriptive (an example of what you would expect would be great).

Edit:
Probably you want to have it grouped by the PROCESSED_REQUESTS.RECSEQ?
Then this might be your solution:
SELECT   PROCESSED_REQUESTS.CREATED_DATE
        ,PROCESSED_REQUESTS.RECSEQ
        ,SUM(PROCESSED_REQUESTS.RESULT)                                                                          AS TOTAL_RECORDS
        ,SUM(CASE WHEN TYPE = 26 THEN PROCESSED_REQUESTS.RESULT ELSE 0 END)                                      AS ONLY_TYPE_26
        ,SUM(CASE WHEN TYPE = 26 THEN PROCESSED_REQUESTS.RESULT ELSE 0 END) / SUM(PROCESSED_REQUESTS.RESULT) * 100 AS PERCENTAGE
    FROM PROCESSED_REQUESTS
         FULL OUTER JOIN MATCHED_RECORDS
             ON (PROCESSED_REQUESTS.RECSEQ = MATCHED_RECORDS.RECSEQ
             AND PROCESSED_REQUESTS.ID = MATCHED_RECORDS.ID
             AND PROCESSED_REQUESTS.FARMID = MATCHED_RECORDS.FARMID)
GROUP BY PROCESSED_REQUESTS.CREATED_DATE, PROCESSED_REQUESTS.RECSEQ
ORDER BY PROCESSED_REQUESTS.CREATED_DATE, PROCESSED_REQUESTS.RECSEQ

Or you just want to count the Records in MATCHED_RECORDS? Then this might help:
SELECT   PROCESSED_REQUESTS.CREATED_DATE
        ,PROCESSED_REQUESTS.RECSEQ
        ,SUM(CASE WHEN MATCHED_RECORDS.TYPE IS NULL THEN 0 ELSE 1 END) AS TOTAL_RECORDS
        ,SUM(CASE WHEN TYPE = 26 THEN 1 ELSE 0 END)                  AS ONLY_TYPE_26
        ,SUM(CASE WHEN TYPE = 26 THEN 1 ELSE 0 END) / COUNT(*) * 100 AS PERCENTAGE
    FROM PROCESSED_REQUESTS
         FULL OUTER JOIN MATCHED_RECORDS
             ON (PROCESSED_REQUESTS.RECSEQ = MATCHED_RECORDS.RECSEQ
             AND PROCESSED_REQUESTS.ID = MATCHED_RECORDS.ID
             AND PROCESSED_REQUESTS.FARMID = MATCHED_RECORDS.FARMID)
GROUP BY PROCESSED_REQUESTS.CREATED_DATE, PROCESSED_REQUESTS.RECSEQ
ORDER BY PROCESSED_REQUESTS.CREATED_DATE, PROCESSED_REQUESTS.RECSEQ

[Updated on: Thu, 09 November 2017 03:27]

Report message to a moderator

Re: query datewise howmany records [message #666502 is a reply to message #666499] Thu, 09 November 2017 04:14 Go to previous message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
SELECT PROCESSED_REQUESTS.CREATED_DATE
,PROCESSED_REQUESTS.RECSEQ
,SUM(CASE WHEN MATCHED_RECORDS.TYPE IS NULL THEN 0 ELSE 1 END) AS TOTAL_RECORDS
,SUM(CASE WHEN TYPE = 26 THEN 1 ELSE 0 END) AS ONLY_TYPE_26 ---> here i need to get the output as 1 only ,because for recseq:1 only I have type=26 records.so i need distinct recseq count as ONLY_TYPE_26 count
,SUM(CASE WHEN TYPE = 26 THEN 1 ELSE 0 END) / COUNT(*) * 100 AS PERCENTAGE
FROM PROCESSED_REQUESTS
FULL OUTER JOIN MATCHED_RECORDS
ON (PROCESSED_REQUESTS.RECSEQ = MATCHED_RECORDS.RECSEQ
AND PROCESSED_REQUESTS.ID = MATCHED_RECORDS.ID
AND PROCESSED_REQUESTS.FARMID = MATCHED_RECORDS.FARMID)
GROUP BY PROCESSED_REQUESTS.CREATED_DATE, PROCESSED_REQUESTS.RECSEQ
ORDER BY PROCESSED_REQUESTS.CREATED_DATE, PROCESSED_REQUESTS.RECSEQ

DATE RECSEQ TOTALCOUNT ONLY_TYPE_26
SYSDATE 1 4 1


TOTALCOUNT Total records came in the day
ONLY_TYPE_26 Recseq should have only 26 type record only,So recseq:1 have MULTIPLE RECORDS then I need to consider as one record only
Previous Topic: Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? split from http://www.orafaq.com/forum/t/161791/
Next Topic: MATERIALIZED VIEW
Goto Forum:
  


Current Time: Thu Mar 28 04:29:03 CDT 2024