SQL - Calculating the MODE

boycee

boycee

boycee

Associate
Joined
23 Mar 2005
Posts
969
Location
Colchester, Essex
Ok.. Calculating the average value (MODE) (Most common) in a column (part of a larger query)

Code: 
STATS_MODE

Was nice, but turns out the stupid legacy DB doesn't support this argument.

Anyone know of a nice way to do it?
 
There's an AVG aggregate function.

EDIT: Sorry that calculates the mean average. What you could do is use a COUNT and GROUP BY, and then sort the results by the COUNT column. You'll have to give more details on exactly what you're trying to do if need any more help. Have you got an example of the table and the results you're trying to achieve?
 
Last edited:
I'd do what stretch recommended.
SELECT value, COUNT(*) valuecount
FROM table
GROUP BY value
ORDER BY valuecount DESC
WHERE ROWNUM=1

(off the top of my head... probably won't work but you get the idea).

You'll have a problem if there's more than one value with the same count, e.g. 100 appears 4 times and so does 200. In that case, I think you need to find the mean average of all those, i.e. 150. You can do this with a subquery to find the largest valuecount first, then the mean average of all values with that valuecount. The query optimizer should pick up the fact you have the same query twice and only perform it once on the data. But with Oracle 9... I wouldn't bank on it.
 
Code: 
select object_type,
       count(object_type) as object_count
  from dba_objects
group
    by object_type
having count(object_type) =
     ( select max(object_count) as highest_total
         from (
              select object_type
                   , count(object_type) as object_count
                from dba_objects
              group
                  by object_type
              ) 
     )
 
You must log in or register to reply here.
Back
Top Bottom