CODEWITHSUNDEEP
sqloracle-database
user2862073
user2862073

Reputation: 254

weekly max count record in oracle

table test has column id,created_date,sal and data like below:

ID create_date sal
1 20-JAN-2021 5000
2 20-JAN-2021 6000
3 19 -JAN- 2021 4000

we have data like this for each date.

I pick the each day record count using below query:

select count(*) ,create_date from test
group by create_date desc
order by 2 desc;

I want output for max record count for a weekly basis:

count(*) create_date
500 20 - JAN-2021
600 10 - JAN - 2021
300 1 - JAN - 2021

Please suggest.

like this.

Please

Upvotes: 0

Views: 46

Answers (1)

Popeye
Popeye

Reputation: 35900

You can use trunc date to iso week and use two level grouping as follows:

Select max(cnt) as max_cnt, 
       trunc(create_date,'IW') as week_start_date
  From
    (select count(*) as cnt, 
            create_date
       from test
      Group by create_date )
group by trunc(create_date,'IW') 
order by 2 desc;

If you want the date also then you need to use analytical function as dollows:

Select * from
     (select count(*) as cnt, 
            create_date,
            Row_number() over (partition by trunc(create_date,'IW') 
                               order by count(*) desc) as rn
       from test
      Group by create_date) t
Where rn = 1

Upvotes: 1

Related Questions