How to select count between start to (start+length) [closed]

CREATE TABLE `new_table`
(
    `start`  int NOT NULL,
    `length` int NOT NULL
);

INSERT INTO `new_table`
VALUES (5, 6),
       (11, 1),
       (21, 10),
       (11, 8),
       (13, 18);

How the following result appears when creating a table as above.

|-----------|----------|
|   1~10    |     1    |
|-----------|----------|
|   11~20   |     4    |
|-----------|----------|
|   21~30   |     2    |
|-----------|----------|
|   31~40   |     2    |

This is the result of counting if the value is included between start plus length.

e.g. : start=5, length=6

This means 5(start)~11(start+lenght)

5<=10 is true

1~10 + 1

11<=20 is true

11~20 + 1

e.g. : start=21, length=10

This means 21(start)~31(start+lenght)

21<=30 is true

21~30 + 1

31<=40 is true

31~40 + 1

e.g. : start=13, length=18

This means 13(start)~31(start+lenght)

(13-20) between (13-31) is included in numbers less than 20.

11~20 + 1

(21-30) between (13-31) is included in numbers less than 30.

21~30 + 1

31 between (13-31) is included in numbers less than 30.

30~40 + 1

I’ve seen how to implement this via googling, but in the end I didn’t get the result. So I tried to implement it further, but it was not enough for me.

Ask for help

Thank you!

Answer

Are you trying to categorize (start + length) into different groups? If so, you can do something like this:

SELECT '>=' || TRIM(length_group), COUNT(*)
FROM (
  SELECT 
    CASE WHEN total_length >= 40 THEN '40' -- Evaluate to true for first match
         WHEN total_length >= 30 THEN '30'
         WHEN total_length >= 20 THEN '20'
         WHEN total_length >= 10 THEN '10'
    END AS length_group
  FROM new_table
) src
GROUP BY length_group

Update
I think I understand what you’re trying to get now. You want to get the ranges of which your (start + length) interval falls into. Is that correct? If so, give this a try:

WITH intervals(start, end) AS (
  SELECT 1, 10 UNION ALL
  SELECT 11, 20 UNION ALL
  SELECT 21, 30 UNION ALL
  SELECT 31, 40
)
SELECT val_group, COUNT(*) AS group_count
FROM (
  SELECT
    CONCAT(CAST(iv.start AS CHAR(3)), '~', CAST(iv.end AS CHAR(3))) AS val_group,
    iv.start
  FROM new_table src
  INNER JOIN intervals iv ON src.start BETWEEN (iv.start - src.length) AND iv.end
) src
GROUP BY val_group
ORDER BY MAX(start), group_count
;

It would be much easier using native data types / functions like INTERVAL and OVERLAP, but it doesn’t seem like MySql has that functionality yet.

DB Fiddle