How to create a select query with count of rows that fall within multiple ranges

Question

I am trying to find a method that creates a new table or a select query that counts the rows that fall within multiple predefined ranges. My current constraint is that I cannot update any table with a new column referencing the table with the categories.

I am trying to create the following table with the count of rows that fall within a category:

Id	category_id	category_name	count
1	1	slow	3
2	2	medium	1
3	3	fast	2

Below is a Schema of table #1 and #2:

Table #1:

Id	category_name	start_range	end_range
1	slow	0	9
2	medium	10	19
3	fast	20	29

Table #2:

Id	test_time
1	3
2	4
3	6
4	14
5	25
6	26

I need to loop each category and find the count for each category.

The following query is not working:

SELECT *, (
    SELECT count(*) 
        FROM temp_times as tt
    WHERE tt.test_time > ttc.start_date AND
        tt.test_time <= ttc.end_date
    ) FROM temp_test_times_classification as ttc
;

The query below builds the temporary tables for the schema i'm using.

-- Table #1
CREATE TEMPORARY TABLE temp_test_times_classification(
   id SERIAL PRIMARY KEY, 
   category_name text,
   start_range int,
   end_range int
);

INSERT INTO 
    temp_test_times_classification (category_name, start_range, end_range)
VALUES
    ('slow', 0,10),
    ('medium', 10,20),
    ('fast', 20,30);


-- Table #2
CREATE TEMPORARY TABLE temp_times(
   id SERIAL PRIMARY KEY, 
   test_time int
);

INSERT INTO 
    temp_times (test_time)
VALUES
(3),
(4),
(6),
(14),
(25),
(26);


CREATE TEMPORARY TABLE expected_results_table(
   id SERIAL PRIMARY KEY, 
   test_times_classification_id INT,
   test_times_count INT
);

INSERT INTO 
    expected_results_table (test_times_classification_id, test_times_count)
VALUES
    (1, 3),
    (2, 1),
    (3, 2);


SELECT * FROM temp_test_times_classification;

SELECT * FROM temp_times;


-- Expected Results table
SELECT  tttct.category_name, ert.test_times_count FROM expected_results_table ert
    INNER JOIN temp_test_times_classification AS tttct
    ON tttct.id = ert.test_times_classification_id
    ;

Gordon Linoff · Accepted Answer · 2021-05-17 10:39:56Z

1

One method is a lateral join:

select *
from table1 t1 left join lateral
     (select count(*) as count
      from table2 t2
      where t2.teset_time between t1.start_range and t1.end_range
     ) t2
     on 1=1;

You can also just use join and group by:

select t1.Id, t1.category_id, t1.category_name,
       count(t2.id) as count
from table1 t1 left join
     table2 t2
     on t2.teset_time between t1.start_range and t1.end_range
group by t1.Id, t1.category_id, t1.category_name

answered May 17, 2021 at 10:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Thorsten Kettner · Accepted Answer · 2021-05-17 11:13:57Z

Please don't ever say "the query is not working" without telling us what is not working. In fact you should be getting the following syntax error message

ERROR:  column ttc.start_date does not exist
LINE 4:     WHERE tt.test_time > ttc.start_date AND
                                 ^
HINT:  Perhaps you meant to reference the column "ttc.start_range".

which does say a lot, doesn't it? Follow the hint, correct the column names in your query and everything runs just fine:

SELECT *, (
    SELECT count(*) 
        FROM temp_times as tt
    WHERE tt.test_time > ttc.start_range AND
        tt.test_time <= ttc.end_range
    ) FROM temp_test_times_classification as ttc
;

Demo: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=cfcde9e81d9ee5f8783d13bdf784d25b

Collectives™ on Stack Overflow

How to create a select query with count of rows that fall within multiple ranges

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related