Link to home
Create AccountLog in
Avatar of hankknight
hankknightFlag for Canada

asked on

MySQL: Get Average Rage of 7 Queries that are NOT NULL

I use the following MySQL query to find the average range of seven queries:

Thanks, Sharath_123, for the answer you posted here:
https://www.experts-exchange.com/questions/28127916/MySQL-Get-Average-of-Seven-Queries.html
SELECT ( ( Max_Bid1 - Min_Bid1 ) + ( Max_Bid2 - Min_Bid2 ) + ( Max_Bid3 - Min_Bid3 ) + ( Max_Bid4 - Min_Bid4 ) + ( Max_Bid5 - Min_Bid5 ) + ( Max_Bid6 - Min_Bid6 ) + ( Max_Bid7 - Min_Bid7 ) ) / 7 Avg_Bid
  FROM (SELECT MAX(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 1 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 1 ) THEN `Bid` 
                   END) Max_Bid1, 
               MIN(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 1 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 1 ) THEN `Bid` 
                   END) Min_Bid1, 
               MAX(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 2 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 2 ) THEN `Bid` 
                   END) Max_Bid2, 
               MIN(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 2 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 2 ) THEN `Bid` 
                   END) Min_Bid2, 
               MAX(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 3 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 3 ) THEN `Bid` 
                   END) Max_Bid3, 
               MIN(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 3 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 3 ) THEN `Bid` 
                   END) Min_Bid3, 
               MAX(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 4 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 4 ) THEN `Bid` 
                   END) Max_Bid4, 
               MIN(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 4 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 4 ) THEN `Bid` 
                   END) Min_Bid4, 
               MAX(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 5 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 5 ) THEN `Bid` 
                   END) Max_Bid5, 
               MIN(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 5 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 5 ) THEN `Bid` 
                   END) Min_Bid5, 
               MAX(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 6 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 6 ) THEN `Bid` 
                   END) Max_Bid6, 
               MIN(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 6 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 6 ) THEN `Bid` 
                   END) Min_Bid6, 
               MAX(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 7 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 7 ) THEN `Bid` 
                   END) Max_Bid7, 
               MIN(CASE 
                     WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 7 ) 
                          AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 7 ) THEN `Bid` 
                   END) Min_Bid7 
          FROM `records` t1, 
               (SELECT `Time` 
                  FROM `records` 
                 ORDER BY `id` DESC 
                 LIMIT 1) t2 
         WHERE `Pair` = 'USDCHF') t3

Open in new window

The problem is that if the range for ANY of these periods is NULL (due to weekends or holidays) then the entire query fails.

How can this be modified so that result returned is the average range for only the periods that have actual values?  

For example if periods 2, 3 and 7 return null results then they should be excluded from the average.

I have attached a database dump containing the structure and some sample data.
pairDump.png
pairDump.sql.txt
Avatar of Sharath S
Sharath S
Flag of United States of America image

Replace each value (Max_Bid1, Min_Bid1, etc) with coalesce(Max_Bid1,0) and so on.
Avatar of hankknight

ASKER

I still get null as the result even with real data:
http://sqlfiddle.com/#!2/5dc83/8
You do not have any data in your sqlfiddle?
Can you try this?
SELECT ( COALESCE(Bid1, 0) + COALESCE(Bid2, 0) 
         + COALESCE(Bid3, 0) + COALESCE(Bid4, 0) 
         + COALESCE(Bid5, 0) + COALESCE(Bid6, 0) 
         + COALESCE(Bid7, 0) ) / ( ( CASE 
                                       WHEN Bid1 IS NULL THEN 0 
                                       ELSE 1 
                                     END ) + ( CASE 
                                                 WHEN Bid2 IS NULL THEN 0 
                                                 ELSE 1 
                                               END ) + ( CASE 
                                                           WHEN Bid3 IS NULL THEN 0 
                                                           ELSE 1 
                                                         END ) + ( CASE 
                                                                     WHEN Bid4 IS NULL THEN 0
                                                                     ELSE 1 
                                                                   END ) + ( CASE 
                                                                               WHEN Bid5 IS NULL THEN 0
                                                                               ELSE 1 
                                                                             END ) + ( CASE 
                                                                                         WHEN Bid6 IS NULL THEN 0
                                                                                         ELSE 1
                                                                                       END ) + ( CASE
                                                                                                   WHEN Bid7 IS NULL THEN 0
                                                                                                   ELSE 1
                                                                                                 END ) )
  FROM (SELECT ( Max_Bid1 - Min_Bid1 ) Bid1, 
               ( Max_Bid2 - Min_Bid2 ) Bid2, 
               ( Max_Bid3 - Min_Bid3 ) Bid3, 
               ( Max_Bid4 - Min_Bid4 ) Bid4, 
               ( Max_Bid5 - Min_Bid5 ) Bid5, 
               ( Max_Bid6 - Min_Bid6 ) Bid6, 
               ( Max_Bid7 - Min_Bid7 ) Bid7 
          FROM (SELECT MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 1 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 1 ) THEN `Bid`
                           END) Max_Bid1, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 1 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 1 ) THEN `Bid`
                           END) Min_Bid1, 
                       MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 2 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 2 ) THEN `Bid`
                           END) Max_Bid2, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 2 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 2 ) THEN `Bid`
                           END) Min_Bid2, 
                       MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 3 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 3 ) THEN `Bid`
                           END) Max_Bid3, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 3 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 3 ) THEN `Bid`
                           END) Min_Bid3, 
                       MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 4 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 4 ) THEN `Bid`
                           END) Max_Bid4, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 4 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 4 ) THEN `Bid`
                           END) Min_Bid4, 
                       MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 5 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 5 ) THEN `Bid`
                           END) Max_Bid5, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 5 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 5 ) THEN `Bid`
                           END) Min_Bid5, 
                       MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 6 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 6 ) THEN `Bid`
                           END) Max_Bid6, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 6 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 6 ) THEN `Bid`
                           END) Min_Bid6, 
                       MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 7 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 7 ) THEN `Bid`
                           END) Max_Bid7, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 7 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 7 ) THEN `Bid`
                           END) Min_Bid7 
                  FROM `records` t1, 
                       (SELECT `Time` 
                          FROM `records` 
                         ORDER BY `id` DESC 
                         LIMIT 1) t2 
                 WHERE `Pair` = 'USDCHF') t3)t4 

Open in new window

fixed again
SELECT ( Bid1 + Bid2 + Bid3 + Bid4+ + Bid5 + Bid6 + Bid7 ) / ( ( CASE 
                                                                   WHEN Bid1 = 0 THEN 0 
                                                                   ELSE 1 
                                                                 END ) + ( CASE 
                                                                             WHEN Bid2 = 0 THEN 0
                                                                             ELSE 1 
                                                                           END ) + ( CASE 
                                                                                       WHEN Bid3 = 0 THEN 0
                                                                                       ELSE 1
                                                                                     END ) + ( CASE
                                                                                                 WHEN Bid4 = 0 THEN 0
                                                                                                 ELSE 1
                                                                                               END ) + ( CASE
                                                                                                           WHEN Bid5 = 0 THEN 0
                                                                                                           ELSE 1
                                                                                                         END ) + ( CASE
                                                                                                                     WHEN Bid6 = 0 THEN 0
                                                                                                                     ELSE 1
                                                                                                                   END ) + ( CASE
                                                                                                                               WHEN Bid7 = 0 THEN 0
                                                                                                                               ELSE 1
                                                                                                                             END ) ) Avg_Bid
  FROM (SELECT ( Max_Bid1 - Min_Bid1 ) Bid1, 
               ( Max_Bid2 - Min_Bid2 ) Bid2, 
               ( Max_Bid3 - Min_Bid3 ) Bid3, 
               ( Max_Bid4 - Min_Bid4 ) Bid4, 
               ( Max_Bid5 - Min_Bid5 ) Bid5, 
               ( Max_Bid6 - Min_Bid6 ) Bid6, 
               ( Max_Bid7 - Min_Bid7 ) Bid7 
          FROM (SELECT MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 1 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 1 ) THEN `Bid`
                             ELSE 0 
                           END) Max_Bid1, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 1 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 1 ) THEN `Bid`
                             ELSE 0 
                           END) Min_Bid1, 
                       MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 2 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 2 ) THEN `Bid`
                             ELSE 0 
                           END) Max_Bid2, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 2 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 2 ) THEN `Bid`
                             ELSE 0 
                           END) Min_Bid2, 
                       MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 3 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 3 ) THEN `Bid`
                             ELSE 0 
                           END) Max_Bid3, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 3 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 3 ) THEN `Bid`
                             ELSE 0 
                           END) Min_Bid3, 
                       MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 4 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 4 ) THEN `Bid`
                             ELSE 0 
                           END) Max_Bid4, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 4 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 4 ) THEN `Bid`
                             ELSE 0 
                           END) Min_Bid4, 
                       MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 5 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 5 ) THEN `Bid`
                             ELSE 0 
                           END) Max_Bid5, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 5 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 5 ) THEN `Bid`
                             ELSE 0 
                           END) Min_Bid5, 
                       MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 6 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 6 ) THEN `Bid`
                             ELSE 0 
                           END) Max_Bid6, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 6 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 6 ) THEN `Bid`
                             ELSE 0 
                           END) Min_Bid6, 
                       MAX(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 7 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 7 ) THEN `Bid`
                             ELSE 0 
                           END) Max_Bid7, 
                       MIN(CASE 
                             WHEN t1.`TIME` > t2.`Time` - 86400 - ( 86400 * 7 ) 
                                  AND t1.`TIME` < t2.`Time` - 72000 - ( 86400 * 7 ) THEN `Bid`
                             ELSE 0 
                           END) Min_Bid7 
                  FROM `records` t1, 
                       (SELECT `Time` 
                          FROM `records` 
                         ORDER BY `id` DESC 
                         LIMIT 1) t2 
                 WHERE `Pair` = 'USDCHF') t3)t4 

Open in new window

Thanks, but it still needs a little tweaking.

Notice that the total range is very small (0.029959976673)
http://sqlfiddle.com/#!2/60197/10

However the code provided returns a much larger number: (0.949609994888)
http://sqlfiddle.com/#!2/60197/4

The average range should be SMALLER than the total range, so it should be a number smaller than 0.029959976673
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account