SQL Frequency and Excel

Hi,

Let's consider the following example in MS Excel (How to Use the FREQUENCY Function[^]):
1. Data in cells A1:A10:

 A1:   2
 A2:   5
 A3:   8
 A4:  11
 A5:  12
 A6:  19
 A7:  21
 A8:  32
 A9:  45
A10:  48

2. Intervals in cells B1:B4:

B1:  10
B2:  20
B3:  30
B4:  40

3. =FREQUENCY(A1:A10,B1:B4) gives the following result:

C1: 3
C2: 3
C3: 1
C4: 1
C5: 2

There is no such built-in function in SQL Server, so you have to build your own query. Here's my sample query for this purpose:

DECLARE @Data TABLE (Value INT);
INSERT INTO @Data (Value) VALUES (2), (5), (8), (11), (12), (19), (21), (32), (45), (48);

DECLARE @Intervals TABLE (Value INT);
INSERT INTO @Intervals (Value) VALUES (0), (10), (20), (30), (40);

;WITH Intervals1
     AS (SELECT Value, ROW_NUMBER() OVER (ORDER BY Value) AS RowNumber
         FROM @Intervals),
     Intervals2
     AS (SELECT t1.Value AS IntervalFrom, t2.Value AS IntervalTo
         FROM Intervals1 AS t1
            LEFT OUTER JOIN Intervals1 AS t2 ON t2.RowNumber = (t1.RowNumber + 1))
SELECT IntervalTo AS Interval, COUNT(Value) AS Frequency
FROM Intervals2, @Data
WHERE (Value > IntervalFrom) AND ( (Value < IntervalTo) OR (IntervalTo IS NULL) )
GROUP BY IntervalFrom, IntervalTo
ORDER BY IntervalFrom;

I'm using CTE to get intervals in two separate columns (IntervalFrom, IntervalTo) and then counting frequencies in these intervals.

Result:

Interval	Frequency
10		3
20		3
30		1
40		1
NULL		2