How can I use SQL to group and count the number of rows where the value for one column is <= x and the value for another column > x?

Question

I'd like to group and count the number of entries in a table that meet criteria colA <= x < colB

Suppose I had the following table:

index  Game            MinAgeInclusive   MaxAgeExclusive
--------------------------------------------------------
1      Candy Land      3                 8
2      Checkers        5                 255
3      Chess           12                255
4      Sorry!          6                 12
5      Monopoly        10                30

(this isn't what I'm doing, but it abstracts away a lot of the other complications with my setup)

Suppose I wanted to get a table that told me how many games were appropriate for different ages:

Age    NumberOfAgeAppropriateGames
----------------------------------
0      0 
...
3      1 
4      1 
5      2
6      3
7      3
8      2
9      2
10     3
...
40     2

I can certainly get the value for a single age:

SELECT 
COUNT(*) 
FROM GameTable 
WHERE MinAgeInclusive <= age AND age < MaxAgeExclusive

And I know how to get the number of items that have a given MaxAgeExclusive

SELECT
MaxAgeExclusive, COUNT(*) AS GameCount
FROM GameTable
GROUP BY MaxAgeExclusive

but I can't quite figure out how to do both.

Since my actual application is doing this on a table with millions of entries, and may have to determine the counts for thousands of values of x, I'm hoping I can maximize performance by doing the whole thing in a single query.

Answer 1

To do this in a reasonably generic way you would probably be best off creating an Auxilliary numbers table for this with a sequence of numbers from 0 to your max value and then use something like the following.

SELECT 
COUNT(*)  AS GameCount, N.Number
FROM Numbers N 
       LEFT OUTER JOIN GameTable ON 
            MinAgeInclusive <= N.Number AND N.Number < MaxAgeExclusive
WHERE N.Number < 100
GROUP BY N.Number