WXforum.net

Web Weather => Weather Website PHP/AJAX scripting => Topic started by: heersje on May 10, 2020, 09:05:39 AM

Title: help with MYSQL query:precipitation to date compared to average
Post by: heersje on May 10, 2020, 09:05:39 AM

Dear all,

My meteobridge stores every 10 minutes data in a MySQL database which I then use to extract information for my website.  I am trying to get my head around the following and wonder if someone has a working solution already.  I want to compare the total rainfall to date  this year with the average rainfall of my station in the same period including previous years. Precipitation is recorded in a column R so MAX(R) gives me the rainfall of a specific day. In PHP I set a variable $days = date(z) to get the number of days in the current year. So I came to the non-working

Code: [Select]

SELECT AVG(sumrain) as AvR from database as A where (SELECT  SUM(MAX(R)) as sumrain, YEAR(DateTime) as Y from database where DAYOFYEAR(DateTime) =< $days group by Y) as B
Any ideas?

Thanks Jean-Paul

Title: Re: help with MYSQL query:precipitation to date compared to average
Post by: heersje on May 11, 2020, 05:26:00 AM

I found a solution for those in future searching for it.

Code: [Select]

$sql = "SELECT ROUND(AVG(SUMR),1) as AVGR from
(SELECT SUM(MAXR) as SUMR, datum from
(SELECT MAX(A.R) as MAXR, Date(A.DateTime) as datum from weatherdata as A
where DATE(A.DAteTime)> Date('2016-12-31')
and DAYOFYEAR(A.DateTime)<=$daynumber
group by DATE(A.DateTime)) as B group by YEAR(datum)) as C";
In PhP I start by defining a variable $daynumber which gives the day number of the year (this as otherwise intervals are not compatible across years) with

Code: [Select]

$daynumber=date(z)I take the average starting after 31-12-2016 (i.e. from 2017 onwards). The database table is weatherdata

good luck!