[SOLVED] How to get the average of a column that is connected to a datetime column in mysql?

Issue

Hello I am trying to get the average of column tc that has a specific date as well as getting its another average in each different date only. Is there a mysql query for it? Here is my weatherdata table:

tc is 31 date is 2022-03-11

tc is 35 date is 2022-03-13

tc is 41 date is 2022-03-14

tc is 100 date is 2022-03-15

My current try of mysqlquery is this

select round(avg(tc),0),date_format(dtime,'%m/%d/%Y') as timeee from weatherdata where DATE(dtime) BETWEEN '2022-03-13' AND '2022-03-15';

I am trying to achieve this one using Python and Matplotlib wherein the dates in mysql is shown in the x axis of the graph and the y values that are plotted are the average of column tc in each different date.

trying to achieve this

Hopefully someone can help me please thanks. Still learning

Solution

It sound to me like you want to have an average for each day. You probably want to use a GROUP BY clause:

SELECT
  DATE_FORMAT(dtime,'%m/%d/%Y') as timeee,
  ROUND(AVG(tc),0)
FROM
    weatherdata
WHERE
    DATE(dtime) BETWEEN '2022-03-13' AND '2022-03-15'
GROUP BY 1
ORDER BY DATE(dtime);

Answered By – JonSG

Answer Checked By – Jay B. (BugsFixing Admin)