SQLite query to get table based on values of another table

user_4724407 0
Viewed 505

I am not sure what title has to be here to correctly reflect my question, I can only describe what I want.

There is a table with fields:

id, name, city

There are next rows:

1 John London
2 Mary Paris
3 John Paris
4 Samy London

I want to get a such result:

      London  Paris
Total   2       2
John    1       1
Mary    0       1
Samy    1       0

So, I need to take all unique values of name and find an appropriate quantity for unique values of another field (city) Also I want to get a total quantity of each city

Simple way to do it is:

1)Get a list of unique names

SELECT DISTINCT name FROM table

2)Get a list of unique cities

SELECT DISTINCT city FROM table

3)Create a query for every name and city

SELECT COUNT(city) FROM table WHERE name = some_name AND city = some_city

4)Get total:

SELECT COUNT(city) FROM table WHERE name = some_name

(I did't test these queries, so maybe there are some errors here but it's only to show the idea)

As there are 3 names and 2 cities -> 3 * 2 = 6 queries to DB

But for a table with 100 cities and 100 names -> 100 * 100 = 10 000 queries to DB and it may take a lot of time to do.

Also, names and cities may be changed, so, I can't create a query with predefined names or cities as every day it's new ones, so, instead of London and Paris it may be Moscow, Turin and Berlin. The same thing with names.

How to get such table with one-two queries to original table using sqlite?

(sqlite: I do it for android)

pivot count group-by sqlite sql
2 Answers

You can get the per-name results with conditional aggregation. As for the total, unfortunately SQLite does not support the with rollup clause, that would generate it automatically.

One workaround is union all and an additional column for ordering:

select name, london, paris
from (
    select name, sum(city = 'London') london, sum(city = 'Paris') paris, 1 prio
    from mytable
    group by name
    union all
    select 'Total', sum(city = 'London'), sum(city = 'Paris'), 0
    from mytable
) t
order by prio, name

Actually the subquery might not be necessary:

select name, sum(city = 'London') london, sum(city = 'Paris') paris, 1 prio
from mytable
group by name
union all
select 'Total', sum(city = 'London'), sum(city = 'Paris'), 0
from mytable
order by prio, name
user_106767160

@GMB gave me the idea of using group by, but as I do it for SQLite on Android, so, the answer looks like:

SELECT name,
COUNT(CASE WHEN city = :london THEN 1 END) as countLondon, 
COUNT(CASE WHEN city = :paris THEN 1 END) as countParis 
FROM table2 GROUP BY name

where :london and :paris are passed params, and countLondon and countParis are fields of the response class

user_47244070
Related