0

I want to query and output a count of countries where residents of each country last travelled to. I have the following query:

SELECT
COUNTRY, SUM(COUNTRY_OF_LAST_TRAVEL = 'USA') AS USA
FROM BASE_TABLE
GROUP BY 
COUNTRY

and the output is something like this:

Country   |   USA
---------   ------   
Argentina |  0
Australia |  293
Bahrain   |  1
....
115 more rows

Let's say I have a table with 3 columns - ID, Country_of_residence, and Country_of_last_travel - It's mostly country data where each ID# corresponds to that member's country of residence, and where they last travel to. How do I output all other country as column so I can output the count? I've tried looking into loops but I'm not getting anywhere with it. Thank you!

Expected result:

Country   |   USA  |   Japan   |   Country n   |
---------   ------   --------
Argentina |  0     |    25     |      0        |
Australia |  293   |    0      |      12       |
Bahrain   |  1     |    10     |      351      |
....
115 more rows

I'm using DBBrowser for SQLite.

bixby
  • 23
  • 3
  • Adding your table and sample data would be helpful – TimLer Aug 04 '21 at 05:09
  • Let's just say I have a table with 3 columns - ID, Country_of_residence, and Country_of_last_travel - It's mostly country data where each ID# corresponds to that member's country of residence, and where they last travel to. – bixby Aug 04 '21 at 05:10
  • @bixby You say that you have a table with three columns (ID ...) but you don't mention the Country column, but in the query, yes. So how is it? – Cesc Aug 04 '21 at 06:00
  • country column = country of residence. hope that clears things out. – bixby Aug 04 '21 at 07:21
  • You need more columns in your query like: `SUM(COUNTRY_OF_LAST_TRAVEL = 'USA') AS USA`, one for each country. – forpas Aug 04 '21 at 08:10
  • is there no other way? that would take more than 100 lines. I was hoping there was a dynamic way to do it. – bixby Aug 04 '21 at 08:51

0 Answers0