Display all data grouped by date in a particular timeframe

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) stat from X)
SELECT 
  status.text,
  COUNT(Y.id) AS c 
from status
left join Y on Y.stat = status.code
group by status.code, status.text;

This gives an output like this:

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

Output for this statement:

If you open my dbfiddle and enter date as 2021-10-01 it gives correct output and if you enter 2021-10-02 it shows correct output. Now I just want a way to show these both together. Also if it is suppose 2021-10-01 and 2021-10-05, it should show everything in middle too which means 2021-10-01, 2021-10-02, 2021-10-03, 2021-10-04 and 2021-10-05

Answer

Your listings.added_date column has the DATETIME data type. Therefore, to select a date range of 2021-10-01 to 2021-10-02 you need to do this.

 WHERE added_date >= '2021-10-01'
   AND added_date <  '2021-10-02' + INTERVAL 1 DAY

This pulls in all the rows from midnight on 1-October, up to but not including midnight on 3-October.

If you want to aggregate your results by day, you can use GROUP BY DATE(added_date).

A sample query — to show all days in September — might look like this:

SELECT DATE(added_date) day,
       SUM(CASE WHEN status.text= 'Action' THEN 1 ELSE 0 END) AS `Action`,
       SUM(CASE WHEN status.text= 'Draft'  THEN 1 ELSE 0 END) AS `Draft`,
       SUM(CASE WHEN status.text= 'Let'    THEN 1 ELSE 0 END) AS `Let`
  FROM tbl
 WHERE added_date >= '2021-09-01'
   AND added_date <  '2021-09-01' + INTERVAL 1 MONTH
 GROUP BY DATE(added_date);

Sorry to say, I don’t understand how your sample query works well enough to rewrite it with GROUP BY. But this should get you started.

Advertisement

Answer