user3529359 user3529359 - 2 months ago 6
SQL Question

get the numbers which occurs maximum time sql

I have a scenario where I have to get the Number which occurs maximum time per Company code. The problem is that when there are two or three numbers which occurs same number of times I have to take the average.

Eg :
Company Code Value
AA 2
AA 2
AA 3
BB 5
BB 5
BB 7
BB 7
BB 9


In the above scenario the output should be
AA 2 - Since 2 occurs twice while 3 occurs only once.
BB 6 - Since for BB both 5 & 7 occurs 2 so I have to take the average.

Thanks In Advance

Answer

In the below query, the following computations are done before getting the final output.

(i) Get the count at this partition level (Company_Code,Value) and assign a rank in descending order.

(ii) Once the rank is assigned select all the rows with rank=1 and average for each Company_Code.

SELECT Company_Code,
       AVG(Value) 
  FROM
     (  
       SELECT Company_Code,
              Value,
              RANK() OVER ( PARTITION BY 
                            Company_Code,Cnt_Company_Code_Value
                            ORDER BY Cnt_Company_Code_Value DESC 
                          )  
           AS Rank_Company_Code_Value 
      FROM     
        (
           SELECT Company_Code, 
                  Value,
                  COUNT(*) OVER ( PARTITION BY Company_Code,Value ) 
                  AS Cnt_Company_Code_Value 
             FROM Table
        )
    )
WHERE Rank_Company_Code_Value = 1
GROUP BY Company_Code;
Comments