mysql - SQLQuery - Counting Values For A Range Across 2 Values Grouped-By 3rd Value in A Single Table -

i have mysql database structured follows: (the numbers on left row numbers)

 +------------+----------+--------------+-------------+  | start_dsgn | end_dsgn | min_cost_1   | min_cost_2  |  +------------+----------+--------------+-------------+ 1|          1 |        2 |            3 |         100 | 2|          1 |        3 |            5 |         153 | 3|          1 |        4 |           10 |         230 | 4|          2 |        1 |            4 |          68 | 5|          2 |        3 |            5 |         134 | 6|          3 |        1 |            7 |          78 | 7|          3 |        2 |            8 |         120 | +------------+----------+--------------+-------------+ 

i query database such each start design, return count of end designs 1 of 2 cost inputs less input. example user inputs value cost_1 , cost_2. query return count of number of rows, grouped design, in cost_1 >= min_cost_1 or cost_2 >= min_cost_2.

so database above lets assume user inputs

cost_1 = 5 cost_2 = 100 

this should return like:

{1:2, 2:2, 3:1} 

where structure is:

{start_design_1: count(row 1 + row 2), start_design_2:count(row 4 + row 5) , start_design_3:count(row 6)} 

i wondering if had tips or solutions on how query relatively new sql. thank much.

also note database not sorted in example, did make example easier follow.

you didn't specify version of sql using doing example in sql server @cost_1 variable. can adapt syntax whatever sql variant using. following give counts want sql result set:

select start_dsgn, count(case when min_cost_1 <= @cost_1 or min_cost_2 <= @cost_2 end_dsng end) cnt table group start_dsgn 

once have that, it's convert whatever format want in whichever language using query database. didn't specify language using, looks want dictionary start_dsgn key.

most languages return list or array of either lists, tuples or dictionaries depending on use. there it's pretty easy convert them other form, @ least in python. typed languages c#, java , swift little trickier.