SQL query to generate a histogram of values based on variable bucket size

Question

i have a table of values that looks like the following:

i am looking for an SQL query to generate a histogram of values for a bucket size that may vary. for example, if the bucket size is 1.0, then the buckets the various counts fall into are [0.0,1.0), [1.0,2.0), [2.0,3.0), [3.0,4.0), ... in this case the query result for the data shown would be:

bucket    count
0.0       1
3.0       1
5.0       1
7.0       3
8.0       1
9.0       1

[EDIT] so far i have the following:

select floor([voltage]/1)*1 as bucket_flr, count(*) as count
from [db1].[dbo].[table]
group by floor([voltage]/1)*1
order by floor([voltage]/1)*1

which seems to be working, but i was wondering if there is something a little more elegant. Thanks!

edited with my attempt

scagnetti
– scagnetti

2016-03-11 19:32:25 +00:00
Commented Mar 11, 2016 at 19:32 — scagnetti
– scagnetti, Commented Mar 11, 2016 at 19:32

Tom H · Accepted Answer · 2016-03-11 21:04:46Z

1

You can use a recursive CTE to get the bucket limits as a result set:

CREATE TABLE dbo.Voltages (voltage DECIMAL(10, 7))

INSERT INTO dbo.Voltages (voltage)
VALUES
(9.7329000),
(7.6746300),
(8.5768950),
(7.9966500),
(3.5873950),
(5.6055000),
(7.5666250),
(0.6287800)

DECLARE @bucket_size DECIMAL(10, 7) = 1.0

;WITH
CTE_MaxVoltage AS (SELECT MAX(voltage) AS voltage FROM Voltages),
CTE_Buckets AS
(
    SELECT
        CAST(0.0 AS DECIMAL(10, 7)) AS bucket_start,
        CAST(0.0 + @bucket_size AS DECIMAL(10, 7)) AS bucket_end,
        1 AS bucket_number
    UNION ALL
    SELECT
        B.bucket_end,
        CAST(B.bucket_end + @bucket_size AS DECIMAL(10, 7)),
        B.bucket_number + 1
    FROM
        CTE_Buckets B
    INNER JOIN CTE_MaxVoltage MV ON MV.voltage > B.bucket_end
)
SELECT
    B.bucket_start AS bucket,
    COUNT(*) AS [count]
FROM
    CTE_Buckets B
INNER JOIN Voltages V ON
    voltage >= B.bucket_start AND
    voltage < B.bucket_end
GROUP BY
    B.bucket_start
ORDER BY
    B.bucket_start

DROP TABLE dbo.Voltages

I had to play around with CAST to get the recursive CTE to work. If you want to see all of the buckets (even those with 0 measurements) then you can change the last JOIN to a LEFT OUTER JOIN and use COUNT(V.voltage) instead of COUNT(*)

edited Mar 11, 2016 at 21:04

answered Mar 11, 2016 at 19:31

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

scagnetti Over a year ago

i am getting the error: GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'CTE_Buckets'.

Tom H Over a year ago

Must be the MAX, which I thought would be ok. Should be easy enough to fix. I'll update the answer.

Collectives™ on Stack Overflow

SQL query to generate a histogram of values based on variable bucket size

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related