Mysql count Same Two or More Same Record as One and Summarize It

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.