We help IT Professionals succeed at work.
Get Started

calcualte median in MySQL

James Rodgers
on
259 Views
Last Modified: 2015-03-17
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
Comment
Watch Question
QA Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE