0

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.

12
  • You might combine what you have with dba.stackexchange.com/questions/224182/… Oct 19, 2021 at 14:10
  • Okay so I tried this dbfiddle.uk/… and it returns all the dates in my listings table and not between that of a date range I choose.
    – Jay Modi
    Oct 19, 2021 at 15:04
  • Indeed, that was not the kind of "combine" I had in mind. I may have some time to look into it later in the evening. In the mean time, you could try to understand what happens in the link I showed you. Oct 19, 2021 at 15:55
  • Alright thanks will look into combining statements
    – Jay Modi
    Oct 19, 2021 at 16:30
  • There are 5 status columns for each date? So, for week you would have 35 columns of counters?
    – Rick James
    Oct 19, 2021 at 17:50

2 Answers 2

1

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;
2
  • Exactly what I wanted. Thanks
    – Jay Modi
    Oct 21, 2021 at 17:54
  • 1
    Please, do some testing by adding some listings and logs to see if the result table changes accordingly. One can't be too careful. Oct 21, 2021 at 18:58
0

Start with a 3-column table

SELECT date, status, count(*)
    FROM ...
    GROUP BY date, status

Then "pivot", using the above as a derived table (that is, FROM (SELECT...)).

Don't bother with WITH, it only confuses the issue.

1
  • Could you please show the output with a dbfiddle for reference
    – Jay Modi
    Oct 19, 2021 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.