CODEWITHSUNDEEP
sqlsqlitejoincountsyntax-error
olaf
olaf

Reputation: 13

Why do my 3 INNER JOIN on same table with different group and count do not work with sqlite?

I have a table with historic migration data. It contains fields for country, region and town where the migrants come from. I want to get a table that shows for each town with 100 or more dataset entries the count of entries together with the region and country it is mentioned together and the respective counts of dataset entries for the region and the country.

If I do simple GROUP BY town the COUNT for region and country will always be within the group of one town so that I can not count the total amount of entries for the region and country. 

country | sum_country | region  | sum_region | town   | sum_town 
Germany | 324,213     | Prussia | 324,213    | Berlin | 324,213

So I tried an INNER JOIN with diferent grouping and count on the same data table (using different alias).

select a.country, c.summe_land, a.last_province, e.summe_region, a.last_town, count (a.last_town) 
from migrant_data as a 

inner join 
(select b.country, count(b.country) as summe_land 
from migrant_data as b group by b.country) as c 
on a.country = c.country

inner join
(select d.last_province, count(d.last_province) as summe_region
from migrant_data as d group by d.last_province) as e
on a.last_province = e.last_province

group by a.country, c.summe_land, a.last_province, e.summe_region, a.last_town 
having count(a.last_town) >99 
order by c.summe_land desc, e.summe_region desc, count (a.last_town) desc;

This worked fine with two inner joins for Country and Region.

country | sum_country | region  | sum_region | town   | sum_town 
Germany | 4,546,321   | Prussia | 864,345    | Berlin | 324,213
Germany | 4,546,321   | Unknown | 3,845,321  | Berlin | 640,139

In the result I discovered that I have a lot of towns that are mentioned in different country or region context (as the historic data are not very consistent). To get an idea of the amount of that phenomenon I want to integrate a third INNER JOIN to count the total amount of entries for each towns in any constellation of country - region - town. The result should look like this:

country | sum_country | region  | sum_region | town   | sum_town | sum_total_town
Germany | 4,546,321   | Prussia | 864,345    | Berlin | 324,213  | 964,352
Germany | 4,546,321   | Unknown | 3,845,321  | Berlin | 640,139  | 964,352

Trying this query in DB Browser for SQLite I get an error message that say: 

'near "(": syntax error:'

Here is my code that does not work:

select a.country, c.summe_land, a.last_province, e.summe_region, a.last_town, g.summe_stadt count (a.last_town) as summe_kombi
from migrant_data as a 

inner join 
(select b.country, count(b.country) as summe_land 
from migrant_data as b group by b.country) as c 
on a.country = c.country

inner join
(select d.last_province, count(d.last_province) as summe_region
from migrant_data as d group by d.last_province) as e
on a.last_province = e.last_province

inner join
(select f.last_town, count(f.last_town) as summe_stadt
from migrant_data as f group by f.last_town) as g
on a.last_town = g.last_town

group by a.country, c.summe_land, a.last_province, e.summe_region, a.last_town, g.summe_stadt 
having count(a.last_town) >99 
order by c.summe_land desc, e.summe_region desc, count (a.last_town) desc;

I double checked my third INNER JOIN but it is structured exactly like the first two INNER JOIN that worked well. I do not know where I should or should not put an bracket or what is wrong next to any of the brackets used.

So I do not know what I did wrong with the third INNER JOIN. I found various examples on stackoverflow that work with three INNER JOIN so it seems possible to aggregate three INNER JOIN. Maybe I overlooked something very simple, I am relativly new to SQL queries (actually I am historian).

Any suggestions? Thanks for your attention!

Upvotes: 1

Views: 61

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

You missed a comma befeore the count(a.last_town) ...

select 
   a.country
  , c.summe_land
  , a.last_province
  , e.summe_region
  , a.last_town
  , g.summe_stadt count(a.last_town) as summe_kombi
                ^^^ here

from migrant_data as a 
...

try using 

  select 
    a.country
    , c.summe_land
    , a.last_province
    , e.summe_region
    , a.last_town
    , g.summe_stadt
    , count (a.last_town) as summe_kombi
  from migrant_data as a 
  inner join  (
        select b.country, count(b.country) as summe_land 
        from migrant_data as b group by b.country) as c on a.country = c.country
  inner join (
        select d.last_province, count(d.last_province) as summe_region
        from migrant_data as d 
        group by d.last_province) as e on a.last_province = e.last_province
  inner join  (
        select f.last_town, count(f.last_town) as summe_stadt
        from migrant_data as f 
        group by f.last_town) as g on a.last_town = g.last_town

  group by a.country, c.summe_land, a.last_province, e.summe_region, a.last_town, g.summe_stadt 
  having count(a.last_town) >99 
  order by c.summe_land desc, e.summe_region desc, count(a.last_town) desc;

Upvotes: 1

Related Questions