The query below does its job to select the min, max, start and last price per day in a given month.
I would like to select the same but for the whole month, as in show the overall performance for the given month instead of on a daily basis.
Fiddle: http://sqlfiddle.com/#!9/ca4867/10
SELECT maxminprice.metal_id,
maxminprice.metal_price_datetime_IST,
maxminprice.max_price,
maxminprice.min_price,
firstlastprice.first_price,
firstlastprice.last_price
FROM (SELECT metal_id,
DATE(metal_price_datetime) metal_price_datetime_IST,
MAX(metal_price) max_price,
MIN(metal_price) min_price
FROM metal_prices
GROUP BY metal_id,
DATE(metal_price_datetime)
ORDER BY metal_id,
DATE(metal_price_datetime_IST)) maxminprice
INNER JOIN (SELECT mp.metal_id,
day_range.metal_price_datetimefl,
SUM(CASE
WHEN TIME(mp.metal_price_datetime_IST) = first_time
THEN
mp.metal_price
ELSE NULL
END) first_price,
SUM(CASE
WHEN TIME(mp.metal_price_datetime_IST) = last_time
THEN
mp.metal_price
ELSE NULL
END) last_price
FROM metal_prices mp
INNER JOIN (SELECT metal_id,
DATE(metal_price_datetime_IST)
metal_price_datetimefl,
MAX(TIME(metal_price_datetime_IST))
last_time,
MIN(TIME(metal_price_datetime_IST))
first_time
FROM metal_prices
GROUP BY metal_id,
DATE(metal_price_datetime_IST))
day_range
ON mp.metal_id = day_range.metal_id
AND DATE(mp.metal_price_datetime_IST) =
day_range.metal_price_datetimefl
AND TIME(mp.metal_price_datetime_IST) IN
( last_time, first_time )
GROUP BY mp.metal_id,
day_range.metal_price_datetimefl) firstlastprice
ON maxminprice.metal_id = firstlastprice.metal_id
AND maxminprice.metal_price_datetime_IST =
firstlastprice.metal_price_datetimefl
AND maxminprice.metal_price_datetime_IST BETWEEN '2018-02-01' AND LAST_DAY('2018-02-01')
ORDER BY metal_id, metal_price_datetime_IST DESC
Here is the query changed to work for each month. I have not changed the column names so you might need to look into doing that for better maintenance. Also, I have not tested this with data for multiple months or over years so you should do that before you start using it.
SELECT maxminprice.metal_id,
maxminprice.metal_price_datetime_IST,
maxminprice.max_price,
maxminprice.min_price,
firstlastprice.first_price,
firstlastprice.last_price
FROM (SELECT metal_id,
DATE_FORMAT(metal_price_datetime_IST, "%Y%m") metal_price_datetime_IST,
MAX(metal_price) max_price,
MIN(metal_price) min_price
FROM metal_prices
GROUP BY metal_id,
DATE_FORMAT(metal_price_datetime_IST, "%Y%m")
ORDER BY metal_id,
DATE_FORMAT(metal_price_datetime_IST, "%Y%m")) maxminprice
INNER JOIN (SELECT mp.metal_id,
day_range.metal_price_datetimefl,
SUM(CASE
WHEN mp.metal_price_datetime_IST = first_time
THEN
mp.metal_price
ELSE NULL
END) first_price,
SUM(CASE
WHEN mp.metal_price_datetime_IST = last_time
THEN
mp.metal_price
ELSE NULL
END) last_price
FROM metal_prices mp
INNER JOIN (SELECT metal_id,
DATE_FORMAT(metal_price_datetime_IST, "%Y%m")
metal_price_datetimefl,
MAX(metal_price_datetime_IST)
last_time,
MIN(metal_price_datetime_IST)
first_time
FROM metal_prices
GROUP BY metal_id,
DATE_FORMAT(metal_price_datetime_IST, "%Y%m")) day_range
ON mp.metal_id = day_range.metal_id
AND DATE_FORMAT(mp.metal_price_datetime_IST, "%Y%m") =
day_range.metal_price_datetimefl
AND mp.metal_price_datetime_IST IN
( last_time, first_time )
GROUP BY mp.metal_id,
day_range.metal_price_datetimefl) firstlastprice
ON maxminprice.metal_id = firstlastprice.metal_id
AND maxminprice.metal_price_datetime_IST =
firstlastprice.metal_price_datetimefl