I'm querying a table but I need the output in different columns

Question

Let's say I have a table, sample below

Member ID  | Hotel ID  | Country | 
----------- ----------- ----------
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

I need to output the count of members in different countries based on different rules:

• Rule 1: Count of members in Countries with members with Hotel ID starting in XX or AA.
• Rule 2: Count of members in Countries with members with Member ID starting with 10.

I can write 2 different queries to have an output like this:

Rule 1
Country  |  Member Count
--------- ----------
Country 4  1
Country 1  2

Rule 2
Country  |  Member Count
--------- ----------
Country 2  2
Country 1  1
Country 3  1

Is there a way to have 1 query and 1 output in different column? Like this:

Country | Rule 1 | Rule 2
-------- -------- --------
Country 1    2       1   
Country 2    0       2
Country 3    0       1 
Country 4    1       0

I'm using DB Browser for SQLite where I created a table and imported the CSV file I'm working on. I know I can use pivot tables for this but it's too slow.

Answer 1

You can group by country and use conditional aggregation:

SELECT Country,
       SUM(HotelID LIKE 'XX%' OR HotelID LIKE 'AA%') Rule1,
       SUM(MemberID LIKE '10%') Rule2
FROM tablename
GROUP BY Country;

Or with SUBSTR() instead of LIKE:

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

See the demo.