>>>>>>Am I supposed to start the program with the first line, because I get an error when I do:
>>>>>>
>>>>>>;with counts as (select Id, count(Id) as frequency
>>>>>
>>>>>I posted you code to try in SQL Server Management Studio if you're using SQL Server.
>>>>>
>>>>>What is your back-end?
>>>>
>>>>I do a lot of work in VFP. Can you suggest a statistics package that I can use within VFP? I've never used one. Thanks, Steve
>>>
>>>I don't know of any. Someone else made that suggestion, hopefully he'll clarify.
>>
>>I have clarified this question, I now have some code in VFP that does at least part of the job, but would still like to know if this can be done solely with SQL, and in VFP? For I suspect that an SQL solution will be easier to expand to include more "levels." I start the question with a reference to "two levels."
>>
>>Consider a table with 2 columns, both numeric: The Qty column contains orange juice package sizes in gallons. The mprices columns shows the cost paid for each unit of gallon. My question: What quantity (in # of gallons) are people most often buying and what is most common price that is being paid. Both cases require dividing the numbers into ranges, and then counting what is happening for each. The result is sort of two histograms with bins: one for qty being purchased, the other for range of prices paid. The kind of report I am looking for is produced by the below code in: myHist.dbf. Apparently I cannot upload prg for dbf files so the code follows:
>
>So you want to know which Qty appears most often and, for each Qty, which price is most common. Correct. Here's a stab at it.
>
>

>SELECT Qty, COUNT(*) AS QtySold ;
>   FROM YourTable ;
>   GROUP BY Qty ;
>   INTO CURSOR csrQtys
>
>SELECT Qty, MAX(QtySold) AS SoldMost ;
>   FROM csrQtys ;
>   INTO CURSOR csrSoldMost
>
>SELECT Qty, MPrices, COUNT(*) AS PriceCount ;
>   FROM YourTable ;
>   GROUP BY Qty, MPrices ;
>   INTO CURSOR csrPriceCounts
>
>SELECT Qty, MPrices, MAX(PriceCount) AS PriceMost ;
>   FROM csrPriceCounts ;
>   GROUP BY Qty, MPrices ;
>   INTO CURSOR csrPriceMost
>

>
>We could cut that down to two queries total by making the first query in each pair into a derived table in the second. That is, we could put the first query (without the INTO clause) into the FROM of the second.
>
>Hope I understood what you were looking for and that this helps.
>
>Tamar

Thank you! The problem remains, however, that because no prices or Qty's repeat, the counts are always 1. For, the above does not create a range of Qty's and prices. So the question is unanswered: How many times do people select, a Qty between, for instance, between .25 and 1.00 gallons and what ranges of prices do they pay for a certain Qty of juice, and how often do select a price that lies within that range of prices? In order words, one has to specify the number of bins for each field, count the occurrences within each, and then filter them to see which is the most "popular." Steve

Examples of it being done:
1. using a function such as round() but then the values better lie within a certain range? http://blog.shlomoid.com/2011/08/how-to-quickly-create-histogram-in.html
2. https://gist.github.com/wolever/9164392
3. Or, automate excel?

I changed the field name of mPrice to myPrices to make things easier to read. The same change would have to be made in range1.prg, (code posted above.)

SELECT Qty, COUNT(*) AS QtySold ;
   FROM myPrices ;
   GROUP BY Qty ;
   INTO CURSOR csrQtys

brow

SELECT Qty, MAX(QtySold) AS SoldMost ;
   FROM csrQtys ;
   GROUP BY Qty, QtySold ;
   INTO CURSOR csrSoldMost

brow

SELECT Qty, myPrices, COUNT(*) AS PriceCount ;
   FROM myPrices ;
   GROUP BY Qty, myPrices ;
   INTO CURSOR csrPriceCounts

BROWSE

SELECT Qty, myPrices, MAX(PriceCount) AS PriceMost ;
   FROM csrPriceCounts ;
   GROUP BY Qty, myPrices ;
   INTO CURSOR csrPriceMost
  
browse