Average of ten minute period

Databases
21

you give up way too easily :slight_smile:

hang on a few minutes, i’ll do it

22 
SELECT avgs.`FROM`
     , avgs.`TO`
     , avgs.`2E-QPSK` AS `avgs_2E-QPSK`
     , data.updated
     , data.`2E-QPSK` AS `data_2E-QPSK`
  FROM ( SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600) AS `FROM`  
              , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(updated))/600)*600+600) AS `TO`
              , round((AVG(`10773`) +                                             
                       AVG(`10788`) +                                             
                       AVG(`10803`) +                                             
                       AVG(`10817`) +                                             
                       AVG(`10862`) +                                             
                       AVG(`10876`) +                                             
                       AVG(`10921`))/7, 2) AS `2E-QPSK`                           
           FROM snr                                                               
         WHERE                                                                    
             updated > '2014-02-06 06:00:00'                                      
         GROUP                                                                    
             BY FLOOR(TIME_TO_SEC(TIME(updated))/600)  
       ) AS avgs                                       
LEFT OUTER 
  JOIN ( SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(snr.updated))/600)*600) AS `FROM`
              , SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(snr.updated))/600)*600+600) AS `TO`
              , ROUND((`10773` + 
                       `10788` + 
                       `10803` + 
                       `10817` + 
                       `10862` + 
                       `10876` + 
                       `10921`)/7, 2) AS `2E-QPSK`
           FROM snr
          WHERE                                 
              updated >= now() - INTERVAL 1 DAY 
       ) AS data
    ON data.`FROM` = avgs.`FROM`
ORDER
    BY data.updated
23

I’m getting an error.

#1054 - Unknown column ‘data.updated’ in ‘field list’

MySQL 5.1.36

24

well, that’s easy to explain

i forgot to put the updated column into the data subquery’s SELECT list

25

Thanks for all the help. Just a couple of questions… is the subquery like having a separate table as far as the outer query is concerned? And why does the result com out in reverse order (newest first)?

26

exactly! :slight_smile:

because i forgot the DESC in the ORDER BY