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
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
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') t3How 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
Sharath S
Replace each value (Max_Bid1, Min_Bid1, etc) with coalesce(Max_Bid1,0) and so on.
ASKER
I still get null as the result even with real data:
http://sqlfiddle.com/#!2/5dc83/8
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
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 ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.