5

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.

Craig S
  • 183
  • 1
  • 1
  • 6
  • 1
    I'm aware that with the example I've given, this sounds vaguely like a homework question; this is probably because I tried to abstract out all the details of my actual problem, and create a new problem that still has some context/makes sense... – Craig S Jul 13 '10 at 11:44
  • What database are you using? And what version? – Mark Byers Jul 13 '10 at 11:45
  • Well, the framework that I'm building into requires that it be somewhat generic; I believe it targets Microsoft SQL, MySQL and Access(!) – Craig S Jul 13 '10 at 11:47

2 Answers2

6

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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • +1, you need to [create a numbers table](http://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table) and then join onto that – KM. Jul 13 '10 at 11:51
  • 1
    Well, I guess I was on the right track: http://stackoverflow.com/questions/3236452 – Craig S Jul 13 '10 at 11:52
1

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.

Kai
  • 2,967
  • 1
  • 22
  • 28
  • shouldn't it be `SELECT 0 AS Age`... in the question's sample output, Age starts at zero and not ten. – KM. Jul 13 '10 at 12:54