best strategy for histogram preparation

[Franco Vaccari]

I'd like to ask what is the best strategy to extract data from a database for producing a histogram. I'm using Xojo ADK.

The way I do it now is to call  SQLselect  in a loop to extract subsets of data and count the records of each subset (see the part // loop over histogram classes in the code below). For histograms made on the values stored in field M1, statements in the loop come out like this:

…
…

select recID,M1,catLink from "Data" where "catLink" = 6 and "M1" >= 5 and "M1" < 5.5
select recID,M1,catLink from "Data" where "catLink" = 6 and "M1" >= 5.5 and "M1" < 6
select recID,M1,catLink from "Data" where "catLink" = 6 and "M1" >= 6 and "M1" < 6.5
…
…

For each query I count the records of the resulting cursor, and use the counts for each range to produce the histogram (e.g. 134, 72, 88, etc, plotted as bars).

It works, but it requires a lot of queries if the number of histogram classes is large. Is there a single SQL command that can produce the same result more efficiently? I mean, return a cursor with  as many fields as the number of histogram classes are, each field containing the count of samples falling in the range? Of course it should be possible to adjust the range definition (i.e. divide the population into 10, 50, 100 etc classes). 

Thanks for any hint

Franco  

=========

Code:

 // inSQLWhereString: the part of the SQL statement that select the ranges of time, coords and depth
 // inMmin: the minimum amplitude from where to start the grouping
 // inMmax: the maximum amplitude for the grouping
 // inMstep: the step of the grouping

 dim ds(-1) as DataSet1D
 dim tmpCursor as vCursor
…
…

// build the SQL part for selecting values of M1 within ranges

         dim j, jStart, jStop,jStep as integer

         jStart=round(inMmin*100)
         jStop=round(inMmax*100)
         jStep=round(inMstep*100)

// loop over histogram classes

         for j = jStart to jStop step jStep

           SQLstring=SQLbaseString+ SQLcatLinkString+_
             " and " + DoubleQuoted("M1") + " >= " + str(j*0.01)+_
             " and " + DoubleQuoted("M1") + " < " + str((j+jStep)*0.01) // interval number

           tmpCursor=mDatabase.SqlSelect(SQLstring,EVCursorLocation.kServerSide,EVLockType.kNoLocks,EVCursorDirection.kRandom)

           dim n as integer = tmpCursor.RecordCount
           tmpCursor=nil

           ds(UBound(ds)).AppendValue n
           ds(UBound(ds)).xLabel.append str(j*0.01)

         next // loop over histogram classes

       end if
 …
…


 return ds