phpmyadmin Question posted in
The official documentation can be found here.

Determine the start of the month to the current date average – Phpmyadmin

As a bit of a background, I have a table which pulls in a daily gas price, I then pull this data into a PHPMyAdmin database and pull/output it on a Grafana dashboard that I have created to show the daily metrics.

I now want to show the average price for the ‘adl_ex_ante_price’ as depicted in the below image. This output needs to be an average of the values pulled into the database from the first day of the current month, until the current day. For example, today it will need to take each of the prices from the 1st Jan to the 11th Jan, where the query outputs the average of the prices from the 1st Jan till the 11 Jan.

Table/Database Format

This is my current query below where I am taking an average of the last 30 days of data, could you please help me edit this query so it takes an average of the data as detailed above.

SELECT gas_date as 'time', AVG(adl_ex_ante_price)
from gas_market_prices 
GROUP BY gas_date
ORDER BY gas_date desc limit 30
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you for the reply, it was nearly working, I had to end up doing a Sub query as depicted below which outputs the correct average:

    SELECT CURDATE() AS 'time', B.price FROM
(SELECT AVG(`adl_ex_ante_price`) AS 'price' FROM `gas_market_prices` WHERE   MONTH(gas_date) = MONTH(CURDATE()) AND YEAR(gas_date) = YEAR(CURDATE())) B

  2. 0

    This should do what you want it to:

    SELECT 
    gas_date AS 'time', 
    AVG(adl_ex_ante_price)
FROM
    gas_market_prices
WHERE
    MONTH(gas_date) = MONTH(CURRENT_DATE())
AND 
    YEAR(gas_date) = YEAR(CURRENT_DATE()) 
GROUP BY 
    gas_date
ORDER BY 
    gas_date DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search