Grouping number field into intervals in a query

[Ksel]

Hi all,

I have a number field called Jobs.SquareFootage, and I want to run a query that categorizes the field into intervals. The intervals I want are:

0 to 50 squarefeet
51 to 300 squarefeet
300+ squarefeet

This is the query I'm coming up with so far

JobID SquareFootage Revenue
1 40 $5
2 30 $10
3 200 $400
4 250 $350
5 550 $600

I want to put the SuareFootage into the intervals I mentioned above, and get the total revenue for each interval. Please help

Thanks,
Kevin

 

[SparklyPrincess6969]

I wrote something that should do it, here's the basic elements:


1. Firstly I have tblCategories, this contains

ID | CategoryName | From | To

where From and To means the lower and upper bounds of the category, I put 9999999 to mean "infinity"

2. First we figure out which category each line item belongs to. Note there's no JOIN (cos there's no key field to join to). So this returns every possible combination of lines. But then we apply a filter "WHERE SquareFootage > From and < To", so each line has the correct Category name next to it.

3. Lastly we use an query to sum up the results of (2) by category, and give the Count of records in each category as well.

Let me know if this is what you were looking for.

 

[LordLoki]

I did not look at the solution of sparklyprincess but you can achieve the wanted result buy using an IIF statement to define the Category. I will post an example later when im back at the Computer

And here is the SQL Code:

SELECT IIf(Jobs.SquareFootage <=50,"0-50",IIf(Jobs.SquareFootage >50 And Jobs.SquareFootage <=300,"51-300","300+")) AS Intervall,Sum(Jobs.Revenue) AS SumOfRevenue
FROM Jobs
GROUP BY IIf(Jobs.SquareFootage <=50,"0-50",IIf(Jobs.SquareFootage >50 And Jobs.SquareFootage <=300,"51-300","300+"));

 

[Ksel]

Thank you both. I'm getting back to work on this a bit later, but I think my question was answered! I'll fiddle around with it.

Cheers!
Kevin

 

[Galaxiom]

Although in this case it could be considered overkill for just three ranges, another alternative is the Partition function to split the ranges.

Partition([fieldname],50,300,251)

Its arguments are Long so consider the potential for rounding that may be required for accuracy if you are going to feed it noninteger input.

It is much simpler than a bunch of nested iifs for larger numbers of equal ranges.

 

[LordLoki]

Thanks Galaxiom thats a very cool function i did not know of.

Greets Loki