CODEWITHSUNDEEP
sql
gberger
gberger

Reputation: 2863

Histogram in SQL without skipping values

Code I have

select 
    floor(value / 5) * 5 as binned_value,
    count(*) as qty
from 
    myTable
group by
    binned_value;

Example

Input

| id | value| 
|----|------|
| 1  | 0    |
| 2  | 4    |
| 3  | 3    |
| 4  | 12   |

Output

| binned_value | qty |
|--------------|-----|
| 0            | 3   |
| 10           | 1   |

Which means there are 3 value between 0..5 and 1 value between 10..15.

Problem

I want the output to be this:

| binned_value | qty |
|--------------|-----|
| 0            | 3   |
| 5            | 0   |
| 10           | 1   |

That is, I want any bin that has 0 qty to also be shown.

I know I can do this with a bunch of joins and unions, but 5, the bin size, is a parameter.

It is possible to know beforehand the highest possible value.

Upvotes: 0

Views: 231

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Here I check what is the max value in myTable to create the bin list first

Here is the sqlFiddle DEMO

;WITH n(n) AS
(
    SELECT 0
    UNION ALL
    SELECT n + 5 
    FROM n 
    WHERE n + 5 < ( select m.Value 
                    from 
                    myTable m
                    where not exists (select Value 
                                      from mytable t 
                                      where m.Value < t.Value
                                     )
                  )
), 
CTE as 
(
  select 
      floor(value / 5) * 5 as binned_value,
      count(*) as qty
  from 
      myTable
  group by
      floor(value / 5) * 5
)
SELECT n AS binned_value,
       ISNULL(qty,0) AS qty
FROM 
    n  LEFT JOIN
    CTE on n.n = CTE.binned_value
OPTION (MAXRECURSION 1000);

Upvotes: 1

TLaV
TLaV

Reputation: 389

You could use recursive CTE to get multiples of 5 (in the example below II got 15, but you could increase it to whatever). Then make your query a subquery and join to it.

WITH NumbersByFive AS
(
    SELECT n = 0
    UNION ALL
    SELECT n + 5 FROM NumbersByFive WHERE n <= 15 

)
SELECT nbf.n AS binned_value,
       ISNULL(qty,0) AS qty
FROM
NumbersByFive nbf
LEFT JOIN
(
    select 
        floor(value / 5) * 5 as binned_value
        ,count(*) as qty
    from myTable b
    group by floor(value / 5) * 5
) Subqry
ON nbf.n = Subqry.binned_Value

Upvotes: 1

Related Questions