[Solved]Work around for Median in SQL Query

[enjoying28]

Hay I need some help. I have a 42k record table that I need the median price for a grouped set of records. Grouped by year then month and then the median (not the avg or mean) of them. I know base does not have a function for median does anyone have code for a macro or something that i could use as a work around?

[Sliderule]

enjoying28:

Just as an FYI, I did see your similar post on the other forum:

http://user.services.openoffice.org/en/forum/viewtopic.php?f=13&t=36695

and no responses.

I suspect, that is because there may not be a 'simple' answer.

Now, if it were me ( infamous last words ), this is how I would accomplish the task. For a quick explanation in English . . . before the 'technical' procedure . . .

1. I would run / save a Query that produces the results "Price" for each column . . . that is . . . each Year and Month.
   
   
2. Copy and Paste the results from the Query into a Calc Spreadsheet . . . using F4 to get the Query results to your Calc Spreadsheet
   
   
3. Using Regular Expressions, replace all values of 0 to nothing . . . that is . . . make sure Regular Expression is selected in the FIND . . . and . . . change ^0 to nothing . . . the reason is to make the entries of 0 ( NULL ) to nothing ( blank ) in Spreadsheet speak.
   
   
4. Use the Calc Function Median . . . for example . . . MEDIAN(A2:A3272) . . . this assumes, just an example, your Query returned 3271 rows ( in addition to the 1 for Column Name )
   
   
5. The above Calc formula can be dragged, so only enter it once, and just drag the formula to each additional column.

Now, the SQL you might use . . . assuming ( I just made this up ) you wanted to see data from your Base file is from 2008-01 to 2010-12. You could merely copy and paste each line as needed to include / exclude more Years / Months

Important Note: The SQL below, I am using a CASE WHEN ELSE END statement . . . therefore . . . it MUST be Run Directly ( using the Toolbar . . . click on the icon with SQL and green check mark ).

[enjoying28]

Thanks for the try I like the idea but there are a few problems when I expanded on your query to take in my three times long time line and two other groups. I endup with a over 500 column query 42K records long and it will not copy and paste into a spreadsheet. I keep trying but there is just two many groups I need. Is it possible to write code to create median just like avg. Because when I run my Avg query on the same data I get four columns aNd just over 600 records. I many have to just back your query up so that I make 40 different one to get the info but there has got to be a better way.

[Sliderule]

enjoying28:

I am not sure I understand what you are saying / asking in your post above.

With your permission, a few questions / comments:

1. What version of OpenOffice are you using?
   
   
2. Which database driver are you using -- put another way, are you using HSQL as the default 'embedded engine' ( version 1.8 ), HSQL version 2.0, MySQL, Microsoft Access, PostGRE, SQLite, some other database driver?
   
   
3. After starting your OpenOffice Base file ( *.odb ), what does the status line say at the bottom?
   
   
4. What I was suggesting should NOT return 42K rows, since, it will have multiple columns and your Where clause is limiting it to "LstStatus" = 'Sold' unless ALL your records have a "LstStatus" of 'Sold'. Furthermore, the number of rows returned should be the greatest number in any one month.
   
   
5. In Calc, by using the F4 key OR from the Calc menu: View -> Data Source
   
   you can drag the Query results to Calc, and have Calc perform the MEDIAN on each column. That way, Calc will be responsible for doing the individual Column sorting and correct calculation of the "middle" / "median" value, whether an even number of values, or, odd.

Sliderule

[enjoying28]

Thanks Silderule
Okay to answer your questions
1. Openoffice 3.2.1 on windows 7 ultimate 32bit
2. HSQL Embedded
3. HSQL Database engine
4. ok my table has more then 42k records about 3 times more but 42K 'LstStatus' sold records my date range is 6/2005 to 11/2010 I have a need to break that up into groups by 'LstArea' five categories and then again by 'LstType' seven categories. At the lowest division there is about 150 to 250 records in any given month. I don't know what you expected it to return but from my count it returns enough rows for every record within the whole time frame. Which sounds right to me because it places the value in the correct column but does not combined records.
5. I was doing this then copying and pasting like you said.

Now after feeling brain died for not knowing I can drag to import the data it works.
I just have to add where that make 35 different query with about 2000 to 5000 records each.
Thanks for the workable solution. I will mark this as solved but one last question. where can I suggest adding Median to the feature set of Base?
Enjoying28

[Sliderule]

You said asked:

[enjoying28]

Thanks

[dacm]

Here's a link to the tutorial for migration of your database to file/server mode, and then upgrade steps to HSQLDB 2.0.x. This requires some steps as you'll find in the tutorial links, but as a Windows user the setup process can be more automated using the available installers.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Migrating 'Embedded databases'

[Sliderule]

enjoying28:

Just so you know . . . perhaps some good news regarding the use of MEDIAN as an AGGREGATE function using HSQL as your database engine.

Per the posts below . . . the Beta Version of HSQL ( as of today ) includes the aggregate function MEDIAN . BUT, it is important to know that this is with HSQL 2.1.0 . . . not the default OpenOffice file as of OpenOffice 3.2.1 nor OpenOffice 3.3 ( when it is available ).

http://sourceforge.net/projects/hsqldb/forums/forum/73674/topic/4019158

The download for the HSQL file . . . hsqldb.jar is found in the first paragraphfound at:

http://www.hsqldb.org/support/index.html

Additionally, the link above from dacm ( thanks so much or your post here ) and at http://user.services.openoffice.org/en/forum/viewtopic.php?f=83&t=17567 can perhaps help you to migrate your current OpenOffice file to HSQL 2.1.0 if you desire.

Sliderule