CODEWITHSUNDEEP
mysqlsqldatabase
LittleLebowski
LittleLebowski

Reputation: 7941

How to find daily average over a time period in mysql?

I've a table where there's two column:

MARKS

CREAT_TS

I want to daily average marks for between two date range (e.g. startDate & endDate)

I've made the following query:

select SUM(MARKS)/ COUNT(date(CREAT_TS)) AS DAILY_AVG_MARKS, 

date(CREAT_TS) AS DATE 

from TABLENAME

group by date(CREAT_TS)

With this query I can get the daily average only if there's a row in the database for the date. But my requirement is that even if there's no row, I want to show 0 for that date. I mean I want the query to return X rows if there are X days between (startDate, endDate)

Can anyone help me. :(

Upvotes: 0

Views: 2592

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You need to create a set of integers that you can add to the dates. The following will give you an idea:

select thedate, avg(Marks) as DAILY_AVG_MARKS
from (select startdate+ interval num day as thedate
      from (select d1.d + 10 * d2.d + 100*d3.d as num
            from (select 0 as d union select 1 union select 2 union select 3 union select 4 union
                  select 5 union select 6 union select 7 union select 8 union select 9
                 ) d1 cross join
                 (select 0 as d union select 1 union select 2 union select 3 union select 4 union
                  select 5 union select 6 union select 7 union select 8 union select 9
                 ) d2 cross join
                 (select 0 as d union select 1 union select 2 union select 3 union select 4 union
                  select 5 union select 6 union select 7 union select 8 union select 9
                 ) d3
            ) n cross join
            (select XXX as startdate, YYY as enddate) const
       where startdate + num <= enddate
      ) left outer join
      tablename t
      on date(CREAT_TS) = thedate
group by thedate

All the complication is in creating a set of sequential dates for the report. If you have a numbers table or a calendar table, then the SQL looks much simpler.

How does this work? The first big subquery has two parts. The first just generates the numbers from 0 to 999 by cross joining the digits 0-9 and doing some arithmetic. The second joins this to the two dates, startdate and enddate -- you need to put the correct values in for XXX and YYY. With this table, you have all the dates between the two values. If you need more than 999 days, just add in another cross join.

This is the left joined to your data table. The result is that all dates appear for the group by.

In terms of reporting, there are advantages and disadvantages to doing this in the presentation layer. Basically, the advantage to doing it in SQL is that the report layer is simpler. The advantage to doing it in the reporting layer is that the SQL is simpler. It is hard for an outsider to make that judgement.

My suggestion would be to create a numbers table that you can just use in reports like this. Then the query will look simpler and you won't have to change the reporting layer.

Upvotes: 4

Related Questions