Search code examples
sqloracle-databasegrouping

Oracle - Count of number of days a value has changed

I have a requirement to write a query in Oracle as shown in the screenshots below. Any help is greatly appreciated. Thanks a lot in advance. Vadi.

enter image description here

enter image description here

Table with sample data:

CREATE TABLE fee_check (
  trans_date DATE,
  fee1 NUMBER(6,3),
  fee2 NUMBER(6,3)
);

INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('18/04/2022','dd/mm/yyyy'), 0.74, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('19/04/2022','dd/mm/yyyy'), 0.75, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('20/04/2022','dd/mm/yyyy'), 0.75, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('21/04/2022','dd/mm/yyyy'), 0.73, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('22/04/2022','dd/mm/yyyy'), 0.73, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('23/04/2022','dd/mm/yyyy'), 0.73, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('24/04/2022','dd/mm/yyyy'), 0.73, 0.87);
INSERT INTO fee_check(trans_date, fee1, fee2) VALUES (to_date('25/04/2022','dd/mm/yyyy'), 0.76, 0.87);
COMMIT;
Solution

  • This is the solution of your problem

    SELECT MIN(trans_date) trans_date, COUNT(*) DayCount, fee1, fee2 
FROM fee_check 
GROUP BY fee1,fee2
ORDER BY trans_date