sql server - SQL query needed for best product mix data -

i work small company dealing herbal ingredients. count regularly effectiveness of ingredients, based on "product mix" (how of ingredient a, b , c). have table thousands of rows, following:

product ingredient ingredient b ingredient c effectiveness
1                          28                               94                            550                      4,1
2 b                         50                               105                           400                      4,3
3 c                         30                               104                           312                       3,5
.. etc etc etc etc etc

what   want result, table below. using excel during last years difficult handle millions of data , therefore have similar in sql. did several attempts pivot , subqueries did not manage result needed.
in particular, in first 3 columns, include various ranges / criteria. in column ‘average effectiveness’ counted average effectiveness of ‘total products’ meet these criteria. due fact ranges hundreds e.g. ingredient a, have more 100 different ranges , ingredient b , c, way have multiple combinations of a, b, c ingredients (ranges) automatically.

ingr.    ingr. b     ingr. c      total products    average effectiveness

1-10       50-60       90-110      ???                            ???
1-10       50-60      110-130     ???                             ??
1-10       50-60      130-150     ????                           ??
1-10       60-70      150-170     ???                             ??
10-20     60-70      90-110       ???                             ??
10-20     60-70      110-130      ???                            ??
10-20     60-70      130-150      ??                              ??
etc etc

i'm unable give more specific answer, think need is;

1. use cube of combinations , aggregate sum , avg values summarizing data using cube

2. the cube query take data nested query has data stored range of value rather actual value. can refer sql's case expression more information on transforming data stores range of value rather value.

so, in other words, first transform data you're storing range value occurs in. transformed data, summarize using cube combinations. #1 outer query , #2 inner query.

here very rough idea of query might like, give idea:

select ingr_a, ingr_b, ingr_c, count(*), avg(effectiveness)      (select       product,       effectiveness,       "ingr_a" =           case              when ingredient_a >= 10 , ingredient_a < 20 '[10, 20)'              when ingredient_a >= 20 , ingredient_a < 30 '[20, 30)'              ...          end,       "ingr_b" =           case             (like above)          end,       "ingr_c"            (etc.)      productstable)   group ingr_a, ingr_b, ingr_c cube