SQL: Count rows for each Range of rows

/*

For a table with INT column count rows for each Range of rows. 

Set to 10M rows...
*/
SELECT
    MIN([KeyTable].[Key]) AS [MinKey],
    MAX([KeyTable].[Key]) AS [MaxKey],
    [KeyTable].[Range] * 10000000 AS [Range10M],
    COUNT(*) AS [RangeRows]
FROM (
    SELECT
        [Key],
        (([Key] - 1) / 10000000) + 1 AS [Range]
    FROM [dbo].[Detail]
) AS [KeyTable]
GROUP BY [KeyTable].[Range]
ORDER BY [KeyTable].[Range]

-- Test one.
SELECT COUNT(*)
FROM [dbo].[Detail]
WHERE [Key]
BETWEEN 150000001 AND 160000000
GO

Comments are closed.

Post Navigation