>>>>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:

Here is range1.prg. Start off with: do range1

SET SAFETY OFF 
clear
CLOSE all
CLOSE DATABASES
PUBLIC mMin,mMax,ftgMin,ftgMax,tRecs,mN,cValue, mDbf, mTitle, zfL, zfH, firstcValue, zC, startN, mCity

CREATE TABLE myHist (					;
bin1 	n(2,0)							,;
lo1 	n(6,2)	, 		hi1 n(6,2)		,;
fq1 	n(5,0)	 						,;
bin2 	n(4,0)							,;
lo2 	n(6,2)	, 		hi2 n(6,2)		,;
fq2 	n(5,0)	;
)

SELECT 1
CREATE TABLE myPrices(qty n(10,6), mprice n(12,2) )

FOR r = 1 TO 5000
	APPEND BLANK
	REPLACE qty WITH RAND()
	REPLACE mprice WITH RAND()*1000
endfor

CLOSE ALL
SELECT 2
USE myHist
SELECT 1
USE myPrices

firstN = 10 && number of bins
firstValue = "qty"  &&--------<<<<<<<<<<<<<<<<============
tRecs = RECCOUNT()
setftgMin = .1 && you can add these in if you don't want total range of values in table
setftgMax = .5 &&-----------<<<<<<<<<<<<<<<<<<<<<<<<<
INDEX on &firstValue TO cIndex
GO top

ftgMax = .00000000000001  && Initialize minimum value  -- use really small/big values
ftgMin = 100000000000000  && Initialize maximum value

SCAN 
   ftgMin = MIN(ftgMin, &firstValue)
   ftgMax = MAX(ftgMax, &firstValue)
ENDSCAN	

*================= insert different value for max and min
ftgMin = setftgMin
ftgMax = setftgMax
*=================

firstmDiff = ABS(ftgMax - ftgMin)  	&& get difference
firstmIncre = firstmDiff / firstN			&& and here is the size of the bin

SELECT 2
USE myHist
SELECT 1
GO top
tCount = 0
FOR startN = 1 TO firstN
	fN = iif(startN=1,ftgMin, ftgMin + (firstmIncre *startN))
			zfL = fN
			zfH = fN + firstmIncre

			SET FILTER TO &firstValue >= zfL AND &firstValue <= zfH
			COUNT TO zC
			tCount = tCount + zC

			zTitle = "call from lot bins " + STR(zfL,10,4)+ STR(zfH,10,4)
			GO top
			SELECT 2
			APPEND BLANK
			GO bott
				REPLACE bin1	WITH startN
				REPLACE lo1 	WITH zfL
				REPLACE hi1 	WITH zfH
				REPLACE fq1 	WITH zC		

					*** insert second range bins and historgram
					IF zC > 0
						DO range2
					endif
					****************
	
			SELECT 1

			q = startN
			z = firstN
			m= DBF()
			SET FILTER TO 
			SET INDEX TO 
			GO top
endFor

SELECT 2
SET TALK off
SUM fq1 TO sF
SET TALK on
mTitle = " end of total lots sold ->" + STR(sF)

GO top
myTop = fq1
GO BOTTOM 
myBott = fq1
myRange = myTop - myBott

BROWSE
*wait
 COPY TO range.csv TYPE csv

Here is range2.prg

SET SAFETY OFF 
clear
CLOSE all
PUBLIC mMin,mMax,tgMin,tgMax,tRecs,mN,cValue, mDbf, mTitle

USE myPrices
SET SAFETY off
SET FILTER TO 
SET DECIMALS TO 4

mN = 5 && number of bins
cValue = "mprice"
tRecs = RECCOUNT()
tgMin = .49 && you can add these in if you don't want total range of values in table
tgMax = 1000
INDEX on &cValue TO cIndex
GO top

gMax = .00000000000001  && Initialize minimum value  -- use really small/big values
gMin = 100000000000000  && Initialize maximum value

SCAN 
   gMin = MIN(gMin, &cValue)
   gMax = MAX(gMax, &cValue)
ENDSCAN	

*================= insert different value for max and min
gMin = tgMin
gMax = tgMax
*=================

mDiff = ABS(gMin - gMax)  	&& get difference
mIncre = mDiff / mN			&& and here is the size of the bin

SELECT 2
USE myHist
SELECT 1
tCount = 0
FOR n = 1 TO mN
		IF n = 1
			fN = gMin
			fL = fN
			fH = fN + mIncre
		ENDIF
		IF n > 1
			fL = fH
			fN = fL
			fH = fL + mIncre			
		ENDIF
		
		IF fN < gMax

			SET FILTER TO;
			&firstValue >= zfL AND &firstValue <= zfH and;
			&cValue >= fL AND &cValue <= fH 

			COUNT TO mC2

			IF mc2 > 0
				GO top
				mcount2 = "count" + STR(mc2) + STR(zfL,10,4) + STR(zfH,10,4) + STR(fL) + STR(fH)
				INDEX on qty * 10^6 + mprice TO k
				GO top
				
				tCount = tCount + mC2

				SELECT 2

				SKIP -1
				mfq = fq1
				skip
				APPEND BLANK
				SKIP -1
					REPLACE bin2	WITH ROUND(n,0)
					REPLACE lo2 	WITH ROUND(fL,2)
					REPLACE hi2 	WITH ROUND(fH,2)
					REPLACE fq2 	WITH mC2		
					REPLACE fq1		WITH zC
					REPLACE bin1 	WITH startN
					REPLACE lo1 	WITH zfL
					REPLACE hi1 	WITH zfH

				IF mc2 > 1000000
					GO top
				endif

			endif
			SELECT 1
			SET FILTER TO 

	endif
endFor

SELECT 2
SET TALK off
SUM fq2 TO sF
SET TALK on
GO top
SET FILTER TO 
SET INDEX TO 
SELECT 1
SET FILTER TO 
SET INDEX TO 

I am hoping to see some more modern ways of doing this. Thank you, Steve