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.

I have 2 separate queries for 1 table. I want to 1 query combining the 2 outputs. — bixby, Jul 30 '21 at 10:07

Serg · Answer 1 · 2021-07-30T10:17:02.420

1

You can use a single query of the form

select Country,
    count(case when <rule1> then 1 end) rule1,
    count(case when <rule2> then 1 end) rule2
from tbl
where (<rule1>) or (<rule2>)
group by Country

db<>fiddle

edited Jul 30 '21 at 10:17

answered Jul 30 '21 at 10:09

Serg

22,285
5
21
48

You may accept the answer to show it works. – Serg Jul 30 '21 at 10:23
Follow up question, supposed I have another table with the same rules and countries but contains data from another region, how do I combine these 2? Thank you! – bixby Jul 30 '21 at 10:41
@bixby . . . New questions should be asked as questions, not comments. – Gordon Linoff Jul 30 '21 at 10:44
@bixby, .. You' d better ask another question and show what the desired output is like – Serg Jul 30 '21 at 10:44

score 1 · Accepted Answer · answered Jul 30 '21 at 10:25

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.

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

2 Answers2