calcualte median in MySQL

I am  trying to determine the median of a column in a table.

given the following values from a  table column
AMOUNT
15.00
100.00
20.00
15.00
15.00
15.00
15.00
15.00
15.00
15.00
15.00

and the following formula/select statement

SELECT x.amount
FROM DONATIONS x , DONATIONS y
GROUP BY x.amount
HAVING SUM(SIGN(1-SIGN(y.amount-x.amount))) = (COUNT(*)+1)/2;

Open in new window

the results  should be  15
however i am getting null, removing the  having clause shows the following output
AMT,             SIGN, COUNT
15.00,      81,       50.0000
20.00,      10,       6.0000
100.00,      11,       6.0000

the actual output should be 15


i have a similar call that does it on  summed values that woirks peerfectly, but non-aggreate values are not working
SELECT   d.amount
FROM     (
                   SELECT    Sum(d.amount) AS amount
                   FROM      donations d
                   LEFT JOIN recipients r
                   ON        d.donation_id = r.donation_id
                   WHERE     donation_status = 'verified'
                   OR        donation_status = 'unverified'
                   GROUP BY  recipient_id) d,
         (
                   SELECT    sum(d.amount) AS amount
                   FROM      gr_donations d
                   LEFT JOIN grdp_recipients r
                   ON        d.donation_id = r.donation_id
                   WHERE     donation_status = 'verified'
                   OR        donation_status = 'unverified'
                   GROUP BY  recipient_id) dd
GROUP BY d.amount
HAVING   sum(sign(1-sign(dd.amount-d.amount))) = (count(*)+1)/2),0 

Open in new window



no idea why the non-aggretate is not working


TIA
LVL 25
James RodgersWeb Applications DeveloperAsked:
Who is Participating?
 
Mark BullockQA Engineer IIICommented:
This query worked for me:
SELECT x.amount 
FROM test.donations x, test.donations y
GROUP BY x.amount
HAVING SUM(SIGN(1-SIGN(y.amount-x.amount)))/COUNT(*) > .5
LIMIT 1;

Open in new window

0
 
James RodgersWeb Applications DeveloperAuthor Commented:
went in a different direction, but this was the base used, B because i already had the base query
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.