SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Get the count and sum using partition by with date range

SeshuGiriAug 20 2014 — edited Aug 20 2014

I have table which contains start date, stop date, amount and etc columns.

I am trying to get count and sum of the amount from date range 01-Aug-2013 to 10-Aug-2013. I am using query below, but instead of giving me the count from 01-Aug to 10-Aug it is getting the total row in the table. How do I fix this?

Note: I am giving only part of my data here, the query I have is very big and I must use partition by .

create table testtbl
(
  STARTD    DATE,
  STOPD     DATE,
  AMT     NUMBER(30,20)
);


SELECT 
DISTINCT 
COUNT(DISTINCT A.STARTD) OVER (PARTITION BY IN_QRY.IN_START,  IN_QRY.IN_STOP) AS A_COUNT,
SUM(A.AMT) OVER (PARTITION BY   IN_QRY.IN_START,  IN_QRY.IN_STOP)  AS SUM_AMT
FROM testtbl A,
 (SELECT 
  TO_DATE('01-AUG-2013') AS IN_START,
  TO_DATE('10-AUG-2013') AS IN_STOP
  FROM DUAL)
IN_QRY;




insert into testtbl (STARTD, STOPD, AMT)
values (to_date('15-07-2013', 'dd-mm-yyyy'), to_date('15-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 9.4340000000000011);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('16-07-2013', 'dd-mm-yyyy'), to_date('16-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 8.4640000000000004);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('17-07-2013', 'dd-mm-yyyy'), to_date('17-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 8.3680000000000003);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('18-07-2013', 'dd-mm-yyyy'), to_date('18-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 10.6870000000000012);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('19-07-2013', 'dd-mm-yyyy'), to_date('19-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 7.4910000000000006);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('20-07-2013', 'dd-mm-yyyy'), to_date('20-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 10.0650000000000013);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('21-07-2013', 'dd-mm-yyyy'), to_date('21-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 15.7500000000000000);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('22-07-2013', 'dd-mm-yyyy'), to_date('22-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 10.9399999999999995);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('23-07-2013', 'dd-mm-yyyy'), to_date('23-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 10.5000000000000000);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('24-07-2013', 'dd-mm-yyyy'), to_date('24-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 11.6600000000000001);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('25-07-2013', 'dd-mm-yyyy'), to_date('25-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 4.2250000000000005);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('26-07-2013', 'dd-mm-yyyy'), to_date('26-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 3.9670000000000001);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('27-07-2013', 'dd-mm-yyyy'), to_date('27-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 3.7660000000000000);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('28-07-2013', 'dd-mm-yyyy'), to_date('28-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 3.0840000000000001);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('29-07-2013', 'dd-mm-yyyy'), to_date('29-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 3.8040000000000003);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('30-07-2013', 'dd-mm-yyyy'), to_date('30-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 3.7800000000000003);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('31-07-2013', 'dd-mm-yyyy'), to_date('31-07-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 3.0580000000000003);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('01-08-2013', 'dd-mm-yyyy'), to_date('01-08-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 4.1170000000000000);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('02-08-2013', 'dd-mm-yyyy'), to_date('02-08-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 12.4720000000000013);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('03-08-2013', 'dd-mm-yyyy'), to_date('03-08-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 25.5609999999999999);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('04-08-2013', 'dd-mm-yyyy'), to_date('04-08-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 34.9750000000000014);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('05-08-2013', 'dd-mm-yyyy'), to_date('05-08-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 20.4950000000000010);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('06-08-2013', 'dd-mm-yyyy'), to_date('06-08-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 27.5730000000000004);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('07-08-2013', 'dd-mm-yyyy'), to_date('07-08-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 20.7420000000000009);


insert into testtbl (STARTD, STOPD, AMT)
values (to_date('08-08-2013', 'dd-mm-yyyy'), to_date('08-08-2013 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 24.0330000000000012);

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 17 2014

Added on Aug 20 2014

8 comments

3,648 views