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.

Improve this question
6
  • Adding your table and sample data would be helpful Commented Aug 4, 2021 at 5: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. Commented Aug 4, 2021 at 5: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? Commented Aug 4, 2021 at 6:00
  • country column = country of residence. hope that clears things out. Commented Aug 4, 2021 at 7:21
  • You need more columns in your query like: SUM(COUNTRY_OF_LAST_TRAVEL = 'USA') AS USA, one for each country. Commented Aug 4, 2021 at 8:10
Related questions
66
How to get a list of column names
648
How to get a list of column names on Sqlite3 database?
0
retrieve columns from sqlite3
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.