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

Answer 2

The age range being checked will be between 10 and 50.

   WITH Age_Tbl
    AS
    (
      SELECT 10 AS Age
      UNION ALL
      SELECT Age+1
      FROM Age_Tbl
      WHERE Age+1 <= 50
    )
    SELECT Age, 
    (select COUNT(*) 
    FROM GameTable G
    WHERE  A.Age between G.Min and G.Max) NumberOfAgeAppropriateGames
 from Age_Tbl A

Edit: As Martin Smith commented, It will work only in MS SQL Server.