Hello,
I am trying to write a subquery that can serve as a poor man's median function.
However I am running into issues every angle I try and cannot find any details
about this in the docs or list archives.
Question 1:
Currently I can calculate the median manually as two steps:
sql> select count(*)/2 from mytable where myIntVal is not null;
+----------------+
| sql_div_count_ |
+================+
| 10 |
+----------------+
sql> select myIntVal as median from mytable order by myIntVal limit 1 offset 10;
+--------+
| median |
+========+
| 1172 |
+--------+
However I cannot figure out how to combine these two queries into one query so
that the count/2 can be done together with the query, so that I don't have to
manually first get the count and then hard code it into the second query that
does the median. How can these be combined?
For instance I tried this which does not work:
sql> select myIntVal as median from mytable order by myIntVal limit 1 offset
(select count(*)/2 from mytable where myIntVal is not null);
but this gives me:
!syntax error, unexpected '(', expecting IDENT or sqlINT
So I assume you can not dynamically substitute a subquery for an OFFSET value.
But is there then another way to combine these statements into one statement?
Question 2:
Once I get the above resolved, I need to be able to get the median as an
aggregate function, so that I can get medians in a group by query for each
element in the group. For instance, if there really was a median() aggregate
function I would do this:
select median(myIntval), customerid from mytable group by customerid;
However of course there is no median function. So I understand the work around
for not having a median function is to get the total rows, sort the rows and
then take the row in the middle.
But how can I do that if it is in a group by statement like the above?
What I am trying desperately to avoid is having to make a query to get all the
group by results, and then for each result having to do a separate individual
query to get its median separately. Please tell me this is possible! :) And if
so, how?
Thank you!!
-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://sourceforge.net/services/buy/index.php
_______________________________________________
Monetdb-developers mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-developers
