Roberto Carlos wrote:
I want to calculate Mean (Average), Median, Mode and Range in Firebird 2.5 and 3.0. I know that Firebird internal function for Mean (Average) is AVG(), but what are the internal functions for Median, Mode and Range in Firebird 2.5 and 3.0? If there are not such functions, how can I calculate them using Firebird?
Svein Erling Tysvær answers:
Don't know whether Firebird has internal functions for this or not and it is likely that there are more elegant solutions available, but the below queries should be ways to get what you ask (although I don't know whether you want to return all values for mode and whether or not you want the average in case of two median values).
execute block returns (mode integer) as
declare variable occurences integer;
declare variable dummy integer;
begin
select <MyField>, count(*)
from <MyTable>
group by 1
order by 2 desc
rows 1
into :dummy, :occurences;
for select <MyField>
from <MyTable>
group by 1
having count(*) = :occurences
into :mode do
suspend;
end
execute block returns (median decimal(9, 1)) as
declare variable occurences integer;
declare variable RowsFrom integer;
declare variable RowsTo integer;
begin
select count(*)
from <MyTable>
into :occurences;
RowsFrom = ((:occurences-1)/2)+1;
RowsTo = (:Occurences/2)+1;
with tmp(tmp) as
(select cast(<MyField> as decimal(9, 1))
from <MyTable>
rows :RowsFrom to :RowsTo)
select sum(tmp)/(:RowsTo-:RowsFrom+1)
from tmp
into :Median;
suspend;
end
select max(<MyField>)-min(<MyField>) as Range
from <MyTable>