0

Good Afternoon all,

I have two same data record or more in MySql table. How i can counting the same data as one and zero if have only one data based on flag status pair. Below is an example of data:

GLOBAL_ID        FLAG_STATUS    
VTSA-C-B002      IN
VTSA-C-B002      OUT
VTSA-C-B003      IN
VTSA-C-B031      IN
VTSA-C-B031      OUT
VTSA-C-B031      OUT
VTSA-C-B002      OUT
VTSA-C-B005      OUT
ABCD-D-B004      IN
ABCD-D-B001      IN
ABCD-D-B003      IN
ABCD-D-B001      OUT
ABCD-D-B003      OUT
ABCD-D-B005      IN

Then The result as i wanted to counting is looks like here

Global ID        Count
VTSA-C-B002      1 (Because have a Pair Flag Status ("IN" AND "OUT")
VTSA-C-B003      0 (Because Dont have a Pair Flag Status ("IN" AND "OUT")
VTSA-C-B031      1
VTSA-C-B005      0
ABCD-D-B004      0
ABCD-D-B001      1
ABCD-D-B003      1
ABCD-D-B005      0

so the Final result to Summarize the counting like this,

Global_ID ALias      SUM of counting
VTSA                 2
ABCD                 2

I have try this query in below, but the result as i looking for

SELECT global_id,
       mandays 
FROM ( SELECT COUNT(DISTINCT b.global_id) AS mandays,
              CASE WHEN b.global_id like "%ABCD%" THEN 'ABCD' ELSE 'VTSA' END AS global_id 
       FROM she_cii.tb_gate_log b 
       WHERE b.global_id REGEXP 'VTSA|ABCD' 
       AND date(b.received_date)="2024-02-03" 
       AND flag_status IN("IN","OUT") 
       group by SUBSTRING(b.global_id FROM POSITION('-' in b.global_id)+1 FOR 5)
      ) x

the result from that query looks like this

global_id   mandays
VTSA        36 (counting all distint global id record)
ABCD        45 (counting all distint global id record)

Thank You Regards.

2 Answers 2

0

Based on your given data example the pairs could easily be found by using

select GLOBAL_ID
from she_cii
group by GLOBAL_ID
having min(FLAG_STATUS) = 'IN' and max(FLAG_STATUS) = 'OUT';

The result would be

GLOBAL_ID
VTSA-C-B002
VTSA-C-B031
ABCD-D-B001
ABCD-D-B003

Now per the count you can use an outer query such as

select SUBSTRING_INDEX(GLOBAL_ID,'-',1) as Global_ID_ALias ,
       count(*) as mandays
from ( 
      select  GLOBAL_ID 
      from she_cii
      group by GLOBAL_ID
      having min(FLAG_STATUS) = 'IN' and max(FLAG_STATUS) = 'OUT'
     ) pair
group by Global_ID_ALias;

Result

Global_ID_ALias mandays
VTSA    2
ABCD    2

See example


Edit

To speed up the query for the given conditions follow below steps

Date function doesn't use an index if present, replace

date(b.received_date)="2024-02-03"

with

received_date >='2024-02-03' and received_date <'2024-02-04'

You are looking for global_id starting with VTSA or ABCD, replace

b.global_id REGEXP 'VTSA|ABCD' 

with

global_id like 'VTSA%' or global_id like 'ABCD%' 

Final query would be

select SUBSTRING_INDEX(GLOBAL_ID,'-',1) as Global_ID_ALias ,
       count(*) as mandays
from ( 
      select  GLOBAL_ID 
      from she_cii
      group by GLOBAL_ID
      having min(FLAG_STATUS) = 'IN' and max(FLAG_STATUS) = 'OUT'
      where flag_status IN ('IN','OUT')
      and ( global_id like 'VTSA%' or global_id like 'ABCD%')
      and received_date >='2024-02-03' and received_date <'2024-02-04'
     ) pair
group by Global_ID_ALias;

Having the index flgst_glbid_recdt(flag_status ,global_id ,received_date ) will speed up the query.

1
  • hello Ergest. Great, Thank you for the answer. I never now the having can do that. for received date i'm not put in on example data because i can use that using Where clause. For REGEXP ' VTSA|OUTOTEC ', there is so many global id format, but i want to calculate some specific global id format. Thank You.
    – epm.007
    Commented Feb 20 at 12:39
0
SELECT DISTINCT
       SUBSTRING_INDEX(global_id, '-', 1) global_id, 
       SUM(COUNT(DISTINCT flag_status) - 1) 
           OVER (PARTITION BY SUBSTRING_INDEX(global_id, '-', 1)) mandays
FROM tb_gate_log
GROUP BY global_id;
global_id mandays
ABCD 2
VTSA 2

fiddle

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.