Calculate mode from series of date of birth

[beulajo]

Hi,

I need to calculate Mean, Median, Minimum, Maximum and Mode
of DOB from a table By calculating their age based on the saved DOB

I have no problem in finding out min,max,mean and median
since built in functions are already available in oracle 10g

I m not able to find mode ..Can you help me to find mode from the list of date of births in one single query and if there is no mode exist, I need not show any rec instead I can show as "NO MODE".

Help me it is bit urgent...

Thanks in advance

[amitpatel66]

Something like this:

[code=oracle]
SQL> ed
Wrote file afiedt.buf

1 with t as (select '01-JAN-2010' dat from dual
2 union all select '01-JAN-2010' from dual
3 union all select '01-JAN-2010' from dual
4 union all select '02-JAN-2010' from dual
5 union all select '02-JAN-2010' from dual)
6 SELECT (CASE WHEN MAX(cnt) > 1 THEN 'MODE' ELSE 'NO MODE' END) mode_Status
7 FROM
8 (SELECT dat, COUNT(*) cnt
9 FROM t
10* GROUP BY dat)
SQL> /

MODE_ST
-------
MODE

SQL> ed
Wrote file afiedt.buf

1 with t as (select '01-JAN-2010' dat from dual
2 union all select '02-JAN-2010' from dual
3 union all select '03-JAN-2010' from dual
4 union all select '04-JAN-2010' from dual
5 union all select '05-JAN-2010' from dual)
6 SELECT (CASE WHEN MAX(cnt) > 1 THEN 'MODE' ELSE 'NO MODE' END) mode_Status
7 FROM
8 (SELECT dat, COUNT(*) cnt
9 FROM t
10* GROUP BY dat)
SQL> /

MODE_ST
-------
NO MODE

SQL>
[/code]