CODEWITHSUNDEEP
sqlitecount
paulojfonseca
paulojfonseca

Reputation: 51

SQLITE - Compare count values on 2 tables with same schema

i am quite new in sqlite.

I have 2 tables (tableA, tableB) with the exact same schema.

id | CAT | country

They track the number of items per country on a special SW release

I would like to create a query that compares the count of rows per country on tableA and tableB where CAT = "AAA" on a single result like:

COUNTRY |count_tableA |count_tableB|
ARG     |12           |16          |
BRA     |23           |33          |

I can achieve it in separate tables but not in a single one. Seperate table example:

select COUNTRY, count(*) as count_tableA from tableA WHERE CAT ="AAA" GROUP BY COUNTRY
select COUNTRY, count(*) as count_tableB from tableB WHERE CAT ="AAA" GROUP BY COUNTRY

Thanks for the help

Upvotes: 0

Views: 216

Answers (2)

Ajax1234
Ajax1234

Reputation: 71451

You can use WITH and COUNT:

with t1 as (select country, count(*) as c1 from tableA where cat = 'AAA' group by country), 
t2 as (select country, count(*) as c1 from tableB where cat = 'AAA' group by country)
select a1.country, 
   ifnull((select t1.c1 from t1 where t1.country = a1.country), 0) as count_tableA, 
   ifnull((select t2.c1 from t2 where t2.country = a1.country), 0) as count_tableB from (
     select distinct country from tableA where cat = 'AAA'
     union
     select distinct country from tableB where cat = 'AAA'
   ) a1
group by country

Upvotes: 0

forpas
forpas

Reputation: 164089

One way to do it is use conditional aggregation on the UNION of the 2 tables:

select country, 
       sum(tablename = 'a') count_tableA,
       sum(tablename = 'b') count_tableB
from (
  select 'a' tablename, id, cat, country from tableA
  union all
  select 'b' tablename, id, cat, country from tableB
)
where cat = 'AAA'
group by country

Upvotes: 2

Related Questions