CODEWITHSUNDEEP
mysqlsql
Régis B.
Régis B.

Reputation: 10618

Create histogram given start and end values in SQL

I have an SQL table with "start" and "end" columns: for the sake of simplicity, let's assume they are integers between 1 and 10. I would like to somehow obtain a histogram of the values between "start" and "end".

For instance, given the following rows:

start end
3 8
4 9

I would like to obtain the following histogram:

time count
1 0
2 0
3 1
4 2
5 2
6 2
7 2
8 2
9 1
10 0

I really have no idea where to start looking in the SQL syntax to get that result -- maybe an inner join?

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can use a recursive CTE to generate times -- if you don't have a handy tally or numbers table. Then join and aggregate:

with recursive cte as (
      select 1 as t
      union all
      select t + 1
      from cte
      where t < 10
     )
select cte.t,
       (select count(*)
        from t
        where cte.t between t.start and t.end
       ) as cnt
from cte;

Here is a db<>fiddle.

Upvotes: 1

Related Questions