I have the following scenario

img

For each year I would like to display the month with the highest number of projects that have ended

I have tried the following so far:

SELECT COUNT(proj.projno) nr_proj, extract(month from proj.end_date) month , extract(year from proj.end_date) year FROM PROJ GROUP BY extract(month from proj.end_date) ,extract(year from proj.end_date)

I am getting the information about the number of projects per month, per year. Could any one give me hints how for each of the years I would select only the records with the highest count of projects?

CodePudding user response：

You can use this solution using max analytic function to get max nr_proj value per year (partition by clause), then keep only rows where nr_proj = mx.

select t.nr_proj, t.month, t.year
from (
SELECT COUNT(proj.projno) nr_proj
    , extract(month from proj.end_date) month
    , extract(year from proj.end_date) year
    , max( COUNT(proj.projno) ) over(partition by extract(year from proj.end_date)) mx
FROM PROJ 
GROUP BY extract(month from proj.end_date), extract(year from proj.end_date)
) t
where nr_proj = mx
;

demo

CodePudding user response：

I think the following will give you what you are after (if I understood the requirements). It fist counts the projects for each month then ranks the months by year, finally it selects the first rank.

select dt "Most Projects Month", cnt "Monthly Projects"
  from ( -- Rank month Count by Year 
         select to_char( dt, 'yyyy-mm') dt
           , cnt 
           , rank() over (partition by extract(year from dt) 
                              order by cnt desc) rnk
        from (-- count number of in month projects for each year         
              select trunc(end_date,'mon') dt, count(*) cnt
                from projects
               group by trunc(end_date,'mon') 
              )
      )
where rnk = 1
order by dt;

NOTE: Not tested, no data supplied. In future do not post images, see Why No Images.