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.
Company Code Value
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;