MySQL – Displaying Data Grouped by Dates in Datewise Order

MySQL

Currently I have 2 tables, a listing table and a logs table. With the following query I'm trying to get the listings of a product on a particular day, and it returns the right output.

with X as (
  select 
    l.*,
    (select status_from from logs
         where logs.refno = l.refno
           and logs.logtime >= '2021-10-01'
         order by logs.logtime limit 1) logstat
  from listings l
  where l.added_date < '2021-10-01'
)
, Y as (select X.*, ifnull(X.logstat, X.status) stat20211001 from X)
SELECT 
  status.text,
  COUNT(Y.id) AS c 
from status
left join Y on Y.stat20211001 = status.code
group by status.code, status.text;

This gives an output like this:

enter image description here

Here I've filtered the query by 1 date which in this case is 2021-10-01. Now I have 2 input forms where the user can select a from date and a to date. So I want to be able to get all the data between the date range provided. So basically if I choose a date between 2021-10-01 and 2021-10-02, it should show everything on and between that date. The output should look like:

Date Publish Action Let Sold Draft
2021-10-01 0 3 0 1 1
2021-10-02 0 2 0 1 2

Dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5e0b8d484a41ac9104d0fb002e7f9a3c

I've formatted the table to show the entries in a row wise manner with the following query:

with X as (
select l.*,
(select status_from from logs where logs.refno = l.refno and logs.logtime >= '2021-10-01' order by logs.logtime limit 1) logstat
from listings l
where l.added_date < '2021-10-01'
)
, Y as (select X.*, ifnull(X.logstat, X.status) stat20211001 from X)
SELECT
sum(case when status.text= 'Action' and Y.id is not null then 1 else 0 end) as `Action`,
sum(case when status.text= 'Draft' and Y.id is not null then 1 else 0 end) as `Draft`,
sum(case when status.text= 'Let' and Y.id is not null then 1 else 0 end) as `Let`,
sum(case when status.text= 'Sold' and Y.id is not null then 1 else 0 end) as `Sold`,
sum(case when status.text= 'Publish' and Y.id is not null then 1 else 0 end) as `Publish`
from status
left join Y on Y.stat20211001 = status.code

Now I just need it to show a new dates column which shows all data on that particular date and have a new entry for each date.

Best Answer

Have a look below and at dbfiddle.uk

with Y as (
  with recursive D (n, day) as (
    select 1 as n, '2021-09-25' my_date
    union
    select n+1, day + interval 1 day from D
      where day + interval 1 day < '2021-10-15'
  ) select * from D
), X as (
  select Y.day,
         l.*,
         (select status_from from logs
            where logs.refno = l.refno
              and logs.logtime >= Y.day
            order by logs.logtime
            limit 1) logstat
    from listings l, Y
    where l.added_date <= Y.day
), Z as (
  select X.day, ifnull(X.logstat,X.status) stat_day, count(*) cnt
    from X
    group by X.day, stat_day
)
select Z.day,
  sum(case when Z.stat_day = 'D' then Z.cnt else 0 end ) Draft,
  sum(case when Z.stat_day = 'A' then Z.cnt else 0 end ) Action,
  sum(case when Z.stat_day = 'Y' then Z.cnt else 0 end ) Publish,
  sum(case when Z.stat_day = 'S' then Z.cnt else 0 end ) Sold,
  sum(case when Z.stat_day = 'L' then Z.cnt else 0 end ) Let
  from Z
  group by Z.day
  order by Z.day;
Related Question