Discussion:
[firebird-support] Basics of Statistics - Mathematics: Mean (Average), Median, Mode and Range ?
Roberto Carlos rc.1990@bol.com.br [firebird-support]
2016-12-01 03:41:26 UTC
Permalink
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]
Svein Erling Tysvær setysvar@gmail.com [firebird-support]
2016-12-01 10:18:08 UTC
Permalink
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
Post by Roberto Carlos ***@bol.com.br [firebird-support]
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]
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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
Roberto Carlos rc.1990@bol.com.br [firebird-support]
2016-12-05 01:52:19 UTC
Permalink
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
</head>







<body style="background-color: #fff;">
<span style="display:none">&nbsp;</span>

<!--~-|**|PrettyHtmlStartT|**|-~-->
<div id="ygrp-mlmsg" style="position:relative;">
<div id="ygrp-msg" style="z-index: 1;">
<!--~-|**|PrettyHtmlEndT|**|-~-->

<div id="ygrp-text" >


<p>Solved. Thanks.

──────────────────────────────────────────────────────────────────

De: "Svein Erling Tysvær ***@gmail.com [firebird-support]"
Enviada: 2016/12/01 08:18:14
Para: firebird-***@yahoogroups.com
Assunto: Re: [firebird-support] Basics of Statistics - Mathematics: Mean (Average), Median, Mode and Range ?



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 , count(*)
from
group by 1
order by 2 desc
rows 1
into :dummy, :occurences;
for select
from
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
into :occurences;
RowsFrom = ((:occurences-1)/2)&#43;1;
RowsTo = (:Occurences/2)&#43;1;
with tmp(tmp) as
(select cast( as decimal(9, 1))
from
rows :RowsFrom to :RowsTo)
select sum(tmp)/(:RowsTo-:RowsFrom&#43;1)
from tmp
into :Median;
suspend;
end

select max()-min() as Range
from

HTH,
Set





</p>

</div>


<!--~-|**|PrettyHtmlStart|**|-~-->
<div style="color: #fff; height: 0;">__._,_.___</div>






<div style="clear:both"> </div>

<div id="fromDMARC" style="margin-top: 10px;">
<hr style="height:2px ; border-width:0; color:#E3E3E3; background-color:#E3E3E3;">
Posted by: Roberto Carlos &lt;***@bol.com.br&gt; <hr style="height:2px ; border-width:0; color:#E3E3E3; background-color:#E3E3E3;">
</div>
<div style="clear:both"> </div>

<table cellspacing=4px style="margin-top: 10px; margin-bottom: 10px; color: #2D50FD;">
<tbody>
<tr>
<td style="font-size: 12px; font-family: arial; font-weight: bold; padding: 7px 5px 5px;" >
<a style="text-decoration: none; color: #2D50FD" href="https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/130102;_ylc=X3oDMTJyMDhvcTZlBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BG1zZ0lkAzEzMDEwMgRzZWMDZnRyBHNsawNycGx5BHN0aW1lAzE0ODA5MDI3NDM-?act=reply&messageNum=130102">Reply via web post</a>
</td>
<td>&bull;</td>
<td style="font-size: 12px; font-family: arial; padding: 7px 5px 5px;" >
<a href="mailto:***@bol.com.br?subject=RE%3A%20%5Bfirebird-support%5D%20Basics%20of%20Statistics%20-%20Mathematics%3A%20Mean%20%28Average%29%2C%20Median%2C%20Mode%20and%20Range%20%3F" style="text-decoration: none; color: #2D50FD;">
Reply to sender </a>
</td>
<td>&bull;</td>
<td style="font-size: 12px; font-family: arial; padding: 7px 5px 5px;">
<a href="mailto:firebird-***@yahoogroups.com?subject=RE%3A%20%5Bfirebird-support%5D%20Basics%20of%20Statistics%20-%20Mathematics%3A%20Mean%20%28Average%29%2C%20Median%2C%20Mode%20and%20Range%20%3F" style="text-decoration: none; color: #2D50FD">
Reply to group </a>
</td>
<td>&bull;</td>
<td style="font-size: 12px; font-family: arial; padding: 7px 5px 5px;" >
<a href="https://groups.yahoo.com/neo/groups/firebird-support/conversations/newtopic;_ylc=X3oDMTJlbGEyZTUxBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA250cGMEc3RpbWUDMTQ4MDkwMjc0Mw--" style="text-decoration: none; color: #2D50FD">Start a New Topic</a>
</td>
<td>&bull;</td>
<td style="font-size: 12px; font-family: arial; padding: 7px 5px 5px;color: #2D50FD;" >
<a href="https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/130077;_ylc=X3oDMTM4dTE3YzVhBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BG1zZ0lkAzEzMDEwMgRzZWMDZnRyBHNsawN2dHBjBHN0aW1lAzE0ODA5MDI3NDMEdHBjSWQDMTMwMDc3" style="text-decoration: none; color: #2D50FD;">Messages in this topic</a>
(3)
</td>
</tr>
</tbody>
</table>


<div id="megaphoneModule">
<hr style="height:2px ; border-width:0; color:#E3E3E3; background-color:#E3E3E3;">
<div>
<div class="stream" style="margin-bottom:10px;">
<div style="background-color:white;">
<div class="sn-img" style="display:inline;"><img name="tn_file" style="padding:0px 10px;vertical-align:top;margin-top:5px;" src="https://s.yimg.com/ru/static/images/yg/img/megaphone/1464031581_phpFA8bON" height="82" width="82"></div>
<div class="mod-txt" style="display:inline-block;">
<a rel="nofollow" name="sub_url" target="_blank" href="https://yho.com/1wwmgg" style="color:#0000FF;display:block;margin-left:5px;text-decoration:none;"><span style="font-size:15px;">Have you tried the highest rated email app?</span></a>
<div style="max-width:530px;padding:2px 5px;">With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.</div>
</div>
</div>
</div> </div>

<hr style="height:2px ; border-width:0; color:#E3E3E3; background-color:#E3E3E3;">
</div>

<!------- Start Nav Bar ------>
<!-- |**|begin egp html banner|**| -->
<!-- |**|end egp html banner|**| -->


<div id="ygrp-grfd" style="font-family: Verdana; font-size: 12px; padding: 15px 0;">

<!-- |**|begin egp html banner|**| -->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++<BR>
<BR>
Visit <a href="http://www.firebirdsql.org">http://www.firebirdsql.org</a> and click the Documentation item<BR>
on the main (top) menu.&nbsp; Try FAQ and other links from the left-side menu there.<BR>
<BR>
Also search the knowledgebases at <a href="http://www.ibphoenix.com/resources/documents/">http://www.ibphoenix.com/resources/documents/</a> <BR>
<BR>
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
<!-- |**|end egp html banner|**| -->

</div>




<!-- |**|begin egp html banner|**| -->
<div id="ygrp-vital" style="background-color: #f2f2f2; font-family: Verdana; font-size: 10px; margin-bottom: 10px; padding: 10px;">

<span id="vithd" style="font-weight: bold; color: #333; text-transform: uppercase; "><a href="https://groups.yahoo.com/neo/groups/firebird-support/info;_ylc=X3oDMTJlaTQ3aGhhBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTQ4MDkwMjc0Mw--" style="text-decoration: none;">Visit Your Group</a></span>

<ul style="list-style-type: none; margin: 0; padding: 0; display: inline;">
<li style="border-right: 1px solid #000; font-weight: 700; display: inline; padding: 0 5px; margin-left: 0;">
<span class="cat"><a href="https://groups.yahoo.com/neo/groups/firebird-support/members/all;_ylc=X3oDMTJmczh2MnYzBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwN2dGwEc2xrA3ZtYnJzBHN0aW1lAzE0ODA5MDI3NDM-" style="text-decoration: none;">New Members</a></span>
<span class="ct" style="color: #ff7900;">3</span>
</li>
</ul>
</div>


<div id="ft" style="font-family: Arial; font-size: 11px; margin-top: 5px; padding: 0 2px 0 0; clear: both;">
<a href="https://groups.yahoo.com/neo;_ylc=X3oDMTJkZTNsNzhtBF9TAzk3NDc2NTkwBGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA2dmcARzdGltZQMxNDgwOTAyNzQz" style="float: left;"><img src="Loading Image..." height="15" width="137" alt="Yahoo! Groups" style="border: 0;"/></a>
<div style="color: #747575; float: right;"> &bull; <a href="https://info.yahoo.com/privacy/us/yahoo/groups/details.html" style="text-decoration: none;">Privacy</a> &bull; <a href="mailto:firebird-support-***@yahoogroups.com?subject=Unsubscribe" style="text-decoration: none;">Unsubscribe</a> &bull; <a href="https://info.yahoo.com/legal/us/yahoo/utos/terms/" style="text-decoration: none;">Terms of Use</a> </div>
</div>
<br>

<!-- |**|end egp html banner|**| -->

</div> <!-- ygrp-msg -->


<!-- Sponsor -->
<!-- |**|begin egp html banner|**| -->
<div id="ygrp-sponsor" style="width:160px; float:right; clear:none; margin:0 0 25px 0; background: #fff;">

<!-- Start Recommendations -->
<div id="ygrp-reco">
</div>
<!-- End Recommendations -->



</div> <!-- |**|end egp html banner|**| -->

<div style="clear:both; color: #FFF; font-size:1px;">.</div>
</div>

<img src="http://geo.yahoo.com/serv?s=97359714/grpId=2442406/grpspId=1705115386/msgId=130102/stime=1480902743" width="1" height="1"> <br>

<img src="http://y.analytics.yahoo.com/fpc.pl?ywarid=515FB27823A7407E&a=10001310322279&js=no&resp=img&cf12=CP" width="1" height="1">

<div style="color: #fff; height: 0;">__,_._,___</div>
<!--~-|**|PrettyHtmlEnd|**|-~-->

</body>

<!--~-|**|PrettyHtmlStart|**|-~-->
<head>
<style type="text/css">
<!--
#ygrp-mkp {
border: 1px solid #d8d8d8;
font-family: Arial;
margin: 10px 0;
padding: 0 10px;
}

#ygrp-mkp hr {
border: 1px solid #d8d8d8;
}

#ygrp-mkp #hd {
color: #628c2a;
font-size: 85%;
font-weight: 700;
line-height: 122%;
margin: 10px 0;
}

#ygrp-mkp #ads {
margin-bottom: 10px;
}

#ygrp-mkp .ad {
padding: 0 0;
}

#ygrp-mkp .ad p {
margin: 0;
}

#ygrp-mkp .ad a {
color: #0000ff;
text-decoration: none;
}
#ygrp-sponsor #ygrp-lc {
font-family: Arial;
}

#ygrp-sponsor #ygrp-lc #hd {
margin: 10px 0px;
font-weight: 700;
font-size: 78%;
line-height: 122%;
}

#ygrp-sponsor #ygrp-lc .ad {
margin-bottom: 10px;
padding: 0 0;
}

#actions {
font-family: Verdana;
font-size: 11px;
padding: 10px 0;
}

#activity {
background-color: #e0ecee;
float: left;
font-family: Verdana;
font-size: 10px;
padding: 10px;
}

#activity span {
font-weight: 700;
}

#activity span:first-child {
text-transform: uppercase;
}

#activity span a {
color: #5085b6;
text-decoration: none;
}

#activity span span {
color: #ff7900;
}

#activity span .underline {
text-decoration: underline;
}

.attach {
clear: both;
display: table;
font-family: Arial;
font-size: 12px;
padding: 10px 0;
width: 400px;
}

.attach div a {
text-decoration: none;
}

.attach img {
border: none;
padding-right: 5px;
}

.attach label {
display: block;
margin-bottom: 5px;
}

.attach label a {
text-decoration: none;
}

blockquote {
margin: 0 0 0 4px;
}

.bold {
font-family: Arial;
font-size: 13px;
font-weight: 700;
}

.bold a {
text-decoration: none;
}

dd.last p a {
font-family: Verdana;
font-weight: 700;
}

dd.last p span {
margin-right: 10px;
font-family: Verdana;
font-weight: 700;
}

dd.last p span.yshortcuts {
margin-right: 0;
}

div.attach-table div div a {
text-decoration: none;
}

div.attach-table {
width: 400px;
}

div.file-title a, div.file-title a:active, div.file-title a:hover, div.file-title a:visited {
text-decoration: none;
}

div.photo-title a, div.photo-title a:active, div.photo-title a:hover, div.photo-title a:visited {
text-decoration: none;
}

div#ygrp-mlmsg #ygrp-msg p a span.yshortcuts {
font-family: Verdana;
font-size: 10px;
font-weight: normal;
}

.green {
color: #628c2a;
}

.MsoNormal {
margin: 0 0 0 0;
}

o {
font-size: 0;
}

#photos div {
float: left;
width: 72px;
}

#photos div div {
border: 1px solid #666666;
height: 62px;
overflow: hidden;
width: 62px;
}

#photos div label {
color: #666666;
font-size: 10px;
overflow: hidden;
text-align: center;
white-space: nowrap;
width: 64px;
}

#reco-category {
font-size: 77%;
}

#reco-desc {
font-size: 77%;
}

.replbq {
margin: 4px;
}

#ygrp-actbar div a:first-child {
/* border-right: 0px solid #000;*/
margin-right: 2px;
padding-right: 5px;
}

#ygrp-mlmsg {
font-size: 13px;
font-family: Arial, helvetica,clean, sans-serif;
*font-size: small;
*font: x-small;
}

#ygrp-mlmsg table {
font-size: inherit;
font: 100%;
}

#ygrp-mlmsg select, input, textarea {
font: 99% Arial, Helvetica, clean, sans-serif;
}

#ygrp-mlmsg pre, code {
font:115% monospace;
*font-size:100%;
}

#ygrp-mlmsg * {
line-height: 1.22em;
}

#ygrp-mlmsg #logo {
padding-bottom: 10px;
}


#ygrp-msg p a {
font-family: Verdana;
}

#ygrp-msg p#attach-count span {
color: #1E66AE;
font-weight: 700;
}

#ygrp-reco #reco-head {
color: #ff7900;
font-weight: 700;
}

#ygrp-reco {
margin-bottom: 20px;
padding: 0px;
}

#ygrp-sponsor #ov li a {
font-size: 130%;
text-decoration: none;
}

#ygrp-sponsor #ov li {
font-size: 77%;
list-style-type: square;
padding: 6px 0;
}

#ygrp-sponsor #ov ul {
margin: 0;
padding: 0 0 0 8px;
}

#ygrp-text {
font-family: Georgia;
}

#ygrp-text p {
margin: 0 0 1em 0;
}

#ygrp-text tt {
font-size: 120%;
}

#ygrp-vital ul li:last-child {
border-right: none !important;
}
-->
</style>
</head>

<!--~-|**|PrettyHtmlEnd|**|-~-->
</html>
<!-- end group email -->

Loading...