CiviCRM Community Forums (archive)

Hi,
We have a a donation campaign right now, with a large numbers of small donations per day. We also accept donations "pay later", and we have a small number of very large donations that are obviously bugus.

The problem is that when you have 10000 donations of 1 and one donation of 10000, the average is 2, but when you take out the outliers, it's one, that is the number that is more meaningful.

So the mathematically obvious solution is to use the median, and this request seems to be not too expensive to run. Could we add it to the contribution report?

SELECT t1.val as median_val FROM (
SELECT @rownum:=@rownum+1 as `row_number`, d.val
  FROM data d,  (SELECT @rownum:=0) r
  WHERE 1
  -- put some where clause here
  ORDER BY d.val
) as t1,
(
  SELECT count(*) as total_rows
  FROM data d
  WHERE 1
  -- put same where clause here
) as t2
WHERE 1
AND t1.row_number=floor(total_rows/2)+1;