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