Geek Answers Handbook

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

I would like to group and count the number of records in a table that meet the 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 is not what I am doing, but it abstracts many other difficulties with my setup)

, , , :

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

, , :

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

, , MaxAgeExclusive

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

, .

, , , x, , , .

+5
2

, , , 0 , - .

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
+6

10 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

: , MS SQL Server.

+1

More articles:


All Articles