sql - Calculate SUM from avg of max value -

i want calculate data 3 times in 1 query. here example table :

date#####| time   |  week  |  machineid |   value | type  1408_1  | 1408_1_1 |  1408  |  001 |    2    | alert  1408_1  | 1408_1_2 |  1408  |  001 |    4    | alert  1408_2  | 1408_1_1 |  1408  |  001 |    5    | alert  1408_2  | 1408_1_2 |  1408  |  001 |    6    | alert  1408_3  | 1408_1_1 |  1408  |  001 |    7    | alert  1408_3  | 1408_1_2 |  1408  |  001 |    5    | alert  1408_4  | 1408_1_1 |  1408  |  001 |    8    | alert  1408_4  | 1408_1_1 |  1408  |  001 |    5    | alert  1408_5  | 1408_1_1 |  1408  |  001 |   10    | alert  1408_5  | 1408_1_1 |  1408  |  001 |    5    | alert  1408_6  | 1408_1_1 |  1408  |  001 |    12   | alert  1408_6  | 1408_1_1 |  1408  |  001 |    5    | alert  1408_7  | 1408_1_1 |  1408  |  001 |    14   | alert  1408_7  | 1408_1_1 |  1408  |  001 |    5    | alert  1409_1  | 1409_1_1 |  1409  |  002 |     9   | alert  1409_1  | 1409_1_1 |  1409  |  003 |   14    | alert 

assume have lot of data, have : 1. each week, there 7 date (1-7), 2. each date, there several time (1-2), 3. each time, there several machine operated (001 - 003)

here expected result example above :

week  | value   |  1408  |  61      |   --> data complete week  1409  | 14       |    

.....up 7 week

first, take maximum data each group by, (let say: group based on machineid)

second, calculate average of maximum number each other group, (group based on date-in case date 1 - 7)

third, sum average calculation before each other group again, (group based on week).

so there 3 group , 3 select,,,

i want display top 7 week based on ascending order...

here code far :

dim strcommand string = "select top 7 week, cast(sum(ap) float) [aplw], cast(avg(ar) float) [arlw] (select top 7 week, cast(sum(maxscorep) float) [sp], cast(avg(maxscorep) float) [ap], cast(sum(maxscorer) float) [sr], cast(avg(maxscorer) float) [ar] " & _      "(select [date] date_, [machineid], week, max(case when [type] = 'alert' cast(jl float) end) [maxscorep], max(case when [type] = 'roll-out' cast(jl float) end) [maxscorer] tbl_tablename group week, [date], [machineid] ) m group [week] order [week] desc) x group [week] order [week] asc" 

that code works fine second select statement, third select (sum) seems error, because doesn't change result. have suggestion problem.

edited code:

        dim strcommand string = " select week, date_, cast(sum(maxscorep) float) [sp], cast(avg(maxscorep) float) [ap] " & _                                 "(select [date] date_, [machineid], week, max(case when [type] = 'pilot' cast(jl float) end) [maxscorep] " & _                                 "tbl_tbl_name group week, [date], [machineid] ) m group [week], [date_] order [week] desc" 

up point, able average each date of data. have suggestion should next group , result each week?

thanks in advance.

too long comment.

your outermost query achieves no change data because grouped [week] before reach outermost layer - , there isn't new in layer.

below suggest change in how top 7, , have removed redundant outermost layer.

select       [week]     , cast(sum(maxscorep) float) [sp]     , cast(avg(maxscorep) float) [ap]     , cast(sum(maxscorer) float) [sr]     , cast(avg(maxscorer) float) [ar] (             select                   [date]                                                     date_                 , [machineid]                 , tbl_tablename.[week]                 , max(case                         when [type] = 'pilot' cast(jl float) end)    [maxscorep]                 , max(case                         when [type] = 'roll-out' cast(jl float) end) [maxscorer]             tbl_tablename                   inner join (                               select top 7                                     [week]                               tbl_tablename                               group                                     [week]                               order                                     [week] desc                         ) w7                               on tbl_tablename.[week] = w7.[week]             group                   tbl_tablename.[week]                 , [date]                 , [machineid]       ) m group       [week] order       [week] asc  

---

note sample data not contain fields used query, , while having data great start knowing want result need.