hankknight
asked on
MySQL: Get Average of Seven Queries
I want the average value for all seven of these queries:
SELECT ((MAX(`Bid`)-MIN(`BID`))) FROM `records` WHERE `Pair` = 'USDCHF'
AND
`Time` > ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-86400-(86400*0))
AND
`Time` < ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-72000-(86400*0))
LIMIT 1
SELECT ((MAX(`Bid`)-MIN(`BID`))) FROM `records` WHERE `Pair` = 'USDCHF'
AND
`Time` > ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-86400-(86400*1))
AND
`Time` < ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-72000-(86400*1))
LIMIT 1
SELECT ((MAX(`Bid`)-MIN(`BID`))) FROM `records` WHERE `Pair` = 'USDCHF'
AND
`Time` > ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-86400-(86400*2))
AND
`Time` < ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-72000-(86400*2))
LIMIT 1
SELECT ((MAX(`Bid`)-MIN(`BID`))) FROM `records` WHERE `Pair` = 'USDCHF'
AND
`Time` > ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-86400-(86400*3))
AND
`Time` < ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-72000-(86400*3))
LIMIT 1
SELECT ((MAX(`Bid`)-MIN(`BID`))) FROM `records` WHERE `Pair` = 'USDCHF'
AND
`Time` > ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-86400-(86400*4))
AND
`Time` < ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-72000-(86400*4))
LIMIT 1
SELECT ((MAX(`Bid`)-MIN(`BID`))) FROM `records` WHERE `Pair` = 'USDCHF'
AND
`Time` > ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-86400-(86400*5))
AND
`Time` < ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-72000-(86400*5))
LIMIT 1
SELECT ((MAX(`Bid`)-MIN(`BID`))) FROM `records` WHERE `Pair` = 'USDCHF'
AND
`Time` > ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-86400-(86400*6))
AND
`Time` < ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-72000-(86400*6))
LIMIT 1
SELECT ((MAX(`Bid`)-MIN(`BID`))) FROM `records` WHERE `Pair` = 'USDCHF'
AND
`Time` > ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-86400-(86400*7))
AND
`Time` < ((SELECT `Time` FROM `records` ORDER BY `id` DESC LIMIT 1 )-72000-(86400*7))
LIMIT 1
corrected some copy-paste errors.
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`,
(SELECT `Time`
FROM `records`
ORDER BY `id` DESC
LIMIT 1) t2
WHERE `Pair` = 'USDCHF') t1
ASKER
can you add some sample data to your table in the sqlfiddle?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. Your query works ONLY if all seven periods contain values.
If any of the values are null then whole thing fails.
I have asked a related question here:
https://www.experts-exchange.com/questions/28128843/MySQL-Get-Average-Rage-of-7-Queries-that-are-NOT-NULL.html
If any of the values are null then whole thing fails.
I have asked a related question here:
https://www.experts-exchange.com/questions/28128843/MySQL-Get-Average-Rage-of-7-Queries-that-are-NOT-NULL.html
Open in new window