Subject Re: [firebird-support] Basics of Statistics - Mathematics: Mean (Average), Median, Mode and Range ?
Author Svein Erling Tysvær
Post date 2016-12-01T10:18:08Z
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>

HTH,
Set

2016-12-01 4:41 GMT+01:00 Roberto Carlos rc.1990@... [firebird-support] <firebird-support@yahoogroups.com>:
Basics of Statistics - Mathematics: Mean (Average), Median, Mode and Range

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?

Thanks for all and any help.


[Non-text portions of this message have been removed]



------------------------------ ------
Posted by: Roberto Carlos <rc.1990@...>
------------------------------ ------

++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++ ++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/ resources/documents/

++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++ ++++++
------------------------------ ------

Yahoo Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/ firebird-support/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/ firebird-support/join
    (Yahoo! ID required)

<*> To change settings via email:
    firebird-support-digest@ yahoogroups.com
    firebird-support-fullfeatured@ yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    firebird-support-unsubscribe@ yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
    https://info.yahoo.com/legal/ us/yahoo/utos/terms/