Author Topic: help with MYSQL query:precipitation to date compared to average  (Read 72 times)

0 Members and 1 Guest are viewing this topic.

Offline heersje

  • Member
  • *
  • Posts: 2
    • Vossemweer
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

Offline heersje

  • Member
  • *
  • Posts: 2
    • Vossemweer
Re: help with MYSQL query:precipitation to date compared to average
« Reply #1 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!
« Last Edit: May 11, 2020, 05:29:27 AM by heersje »