Comparing and Matching keybinds while comparing 2 tables and returning count

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')
select X.*, ifnull(X.logstat,X.status) stat20211001 from X;

I’ve tried the following in this dbfiddle(https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b0e433b59b5c4a4a0be3f0bc25d20124) with the following table for listings:

 (3, 'Publish','2021-05-02','2021-10-02','LP01'), (4, 'Action','2021-05-01','2021-05-01','LP02'),
 (5, 'Sold','2020-10-01','2020-10-01','LP03'), (6, 'Let','2021-05-01','2021-10-06','LP06'), 
 (10, 'Draft','2021-10-06','2021-10-06','LP05'), (11, 'Draft','2021-01-01','2021-01-01','LP04');

But now in my actual database, the statuses are represented by a particular keybind. For example, (‘D’ => ‘Draft’, ‘A’ => ‘Action’, ‘Y’ => ‘Publish’, ‘S’ => ‘Sold’, ‘N’ => ‘Let’):

So basically that makes my actual listings table with the following data:

 (3, 'Y','2021-05-02','2021-10-02','LP01'), (4, 'A','2021-05-01','2021-05-01','LP02'),
 (5, 'S','2020-10-01','2020-10-01','LP03'), (6, 'N','2021-05-01','2021-10-06','LP06'), 
 (10, 'D','2021-10-06','2021-10-06','LP05'), (11, 'D','2021-01-01','2021-01-01','LP04');

Now I want a way to have the same output of my query above, but show it with the new data and instead of showing The output it is showing right now in words, it should group similar statuses and return a count for that data. For example:

Basically I want something like this to be added to the statement I think:

SELECT case status
when 'D' THEN 'Draft'
when 'A' THEN 'Action'
when 'Y' THEN 'Publish'
when 'S' THEN 'Sold'
when 'N' THEN 'Let'
END status_l ,COUNT(*) c from listings
group by status

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I suggest you create a status table in your database. Use this table, outer join your query and count:

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;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=673125506bb29b9d10719010aa2e00ae

Method 2

Looks like you need in something like

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'),
statustable AS (SELECT 'Draft' status UNION ALL
                SELECT 'Action' UNION ALL
                SELECT 'Publish' UNION ALL
                SELECT 'Sold' UNION ALL
                SELECT 'Let')
select X.*,
       COALESCE(X.logstat, statustable.status) stat20211001
from X
LEFT JOIN statustable ON X.status = LEFT(statustable.status, 1);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6b65365be8c3403b329a1b4b56212a03

Hmm no I tried this, but it does not give the right output. The output I want is something like the table I have provided above – JJM50

My query prepares everything for final grouping – I thought that was enough…

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'),
statustable AS (SELECT 'Draft' status UNION ALL
                SELECT 'Action' UNION ALL
                SELECT 'Publish' UNION ALL
                SELECT 'Sold' UNION ALL
                SELECT 'Let'),
preparedata AS ( select COALESCE(X.logstat, statustable.status) stat20211001
                 from X
                 LEFT JOIN statustable ON X.status = LEFT(statustable.status, 1) )
SELECT t1.status, COUNT(t2.stat20211001)
FROM statustable t1
LEFT JOIN preparedata t2 ON t1.status = t2.stat20211001
GROUP BY t1.status

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0edec9fbd3f07892e70f7a549117af8d

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0