Comparing and Matching keybinds while comparing 2 tables and returning count-CodePudding

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:

status	Count
Publish(Y)	0
Action(A)	3
Let(N)	0
Sold(S)	1
Draft(D)	1

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

CodePudding user response：

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

CodePudding user response：

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, status.text;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=17c8b992ea4a943ef9bb7fa81b4c2748