SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (`MemberID` INTEGER, `HotelID` TEXT, `Country` TEXT);
INSERT INTO tablename (`MemberID`, `HotelID`, `Country`) VALUES
  ('100', 'ZZA', 'Country 1'),
  ('101', 'ZZA', 'Country 2'),
  ('102', 'ZZB', 'Country 2'),
  ('103', 'ZZC', 'Country 3'),
  ('201', 'XXD', 'Country 4'),
  ('202', 'XXE', 'Country 1'),
  ('203', 'AAB', 'Country 1');
SELECT Country,
       SUM(HotelID LIKE 'XX%' OR HotelID LIKE 'AA%') Rule1,
       SUM(MemberID LIKE '10%') Rule2
FROM tablename
GROUP BY Country;


Country
Rule1
Rule2


Country 1
2
1

Country 2
0
2

Country 3
0
1

Country 4
1
0


SELECT Country,
       SUM(SUBSTR(HotelID, 1, 2) IN ('XX', 'AA')) Rule1,
       SUM(SUBSTR(MemberID, 1, 2) = '10') Rule2
FROM tablename
GROUP BY Country;


Country
Rule1
Rule2


Country 1
2
1

Country 2
0
2

Country 3
0
1

Country 4
1
0