MySQL Forums
Forum List  »  Stored Procedures

MySql Percentile works in Select statement but not Function - ?
Posted by: Jason Wisdom
Date: March 29, 2021 07:11PM

Hi everybody,
I am trying to calculate Percentile of values.
Basically, the SELECT statement works fine, scoring Testid=1 at the 60th percentile where it should be. But the FUNCTION with exact same select statement, always gives 100th percentile.
Any idea why?
Thank you,
Jason


--


My SELECT statement works fine:

select
dt.readingid, round(100*(ct.cnt-rank+1)/ct.cnt,0) as totalchempct
from
(SELECT
rc.readingid,
@curRank := @curRank + 1 AS rank
FROM (select readingid, sum(weight*chemvalue) as chemistry from readingchem where chemdimid=1 group by readingid) rc,
(SELECT @curRank := 0) r
ORDER BY rc.chemistry desc) as dt,
(select count(distinct readingid) as cnt from readingchem) as ct
where dt.readingid=1;

I get back "60", as reading #1's test results are the 60th percentile of all results.

But the FUNCTION, with this same pasted query, always returns "100" and never "60":

--

DELIMITER $$
CREATE FUNCTION `fngetchemdimpct`(chemdimid int, readingid int) RETURNS decimal(10,4)
BEGIN

declare chempct decimal(10,4);

SET chempct = (
select
round(100*(ct.cnt-rank+1)/ct.cnt,0)
from
(SELECT
rc.readingid,
@curRank := @curRank + 1 AS rank
FROM (select readingid, sum(weight*chemvalue) as chemistry from readingchem where chemdimid=1 group by readingid) rc,
(SELECT @curRank := 0) r
ORDER BY rc.chemistry desc) as dt,
(select count(distinct readingid) as cnt from readingchem) as ct
where dt.readingid=1);

RETURN chempct;
END$$
DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
MySql Percentile works in Select statement but not Function - ?
118
March 29, 2021 07:11PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.