That query requires some attention as I get a syntax error when I try to run it - that GROUP BY needs to be outside the brackets. You should always provide some sample data with questions like this, as well as your expected results and actual results.
First and foremost, MySQL is a set-based language - there is no need to "loop" through anything.
I used the following sample data derived from your other question (you owe thanks to @maciej-los for that)
CREATE TABLE KJ_log (tid datetime, mycall varchar(10), mm varchar(10), conf varchar(10), sm varchar(10));
insert into KJ_log(mycall ,tid, mm, conf, sm) values
('SM6ABC', '2022-04-01 09:00:00', 'AAAA', '1','xxx'),
('SM6ABC', '2022-04-01 09:01:00', 'AAAB', '1','xxx'),
('SM6ABC', '2022-04-01 09:12:00', 'AAAA', '1','xxx'),
('SM6CBA', '2022-04-01 09:10:00', 'BBBB', '1','xxx'),
('SM6CBA', '2022-04-01 09:11:00', 'AAAA', '1','xxx'),
('SM6AAA', '2022-04-01 09:12:00', 'AAAA', '1','xxx');
Next you can either use a CTE (use the link provided in Solution 1) or a temporary table - I chose to put the data into a table while I was checking it out
create table t1 (mycall varchar(10), sm varchar(10));
insert into t1
(
select mycall, sm from
(
SELECT DISTINCT mycall
, sm FROM KJ_log
WHERE
sm !=''
AND conf = '1'
UNION DISTINCT
SELECT mycall
, mm FROM KJ_log
WHERE
mm !='' AND conf = '1'
) AS x
);
NOTE - I have removed the part of the WHERE clause that refers to
mycall
I then used the following
SELECT sm, COUNT(MYCALL), MAX(KJ_tid)
FROM
(
SELECT
t1.*
, KJ_log.tid AS KJ_tid
, KJ_log.sm AS KJ_sm
, KJ_log.mm AS KJ_mm
FROM t1
LEFT JOIN KJ_log ON KJ_log.mycall = t1.mycall AND (t1.sm = KJ_log.sm OR t1.sm = KJ_log.mm)
where conf = '1'
) as LatestDate
GROUP BY sm
NOTE the addition of the filter on
KJ_log.mycall
in the ON clause of the join. If you omit that you will effectively end up with a cross-join and many many repeated rows.
NOTE also the addition of
sm
to the outer SELECT clause - it is usual to show the items that you have grouped by in such scripts