Let's assume that I can only do this in SQLite.

I have two Tables.

```
State {
DistrictID: int
SubdistrictID: int
CityID: Int
name: varchar(36)
}
```

Theres an obvious heirarchy in the State table. A State has a number of Districts, Each district has a number of Subdistricts, each Subdistrict has a number of Cities.

The other table is the number of Students in a City, where the Students.CityID = State.CityID

```
Students {
CityID: int
number: int
}
```

I want to know take out the number of Students existing in a District '221'.

**QUERY**: `SELECT sum(students.number) from State LEFT JOIN Students students ON State.CityID = students.CityID WHERE State.DistrictID = 221 GROUP BY State.DistrictID`

So far so good, this does work. I get the total number of students from across all the cities that are in District 221.

### Heres the complication.

Some **Cities are SHARED between Subdistricts**. In this case, a District have One city thats shared across two Subdistricts. This is reflected within the State Table.

```
State Table
Row0: DistrictID: 221; SubDistrictID: 332; CityID: 554
Row1: DistrictID: 221; SubDistrictID: 332; CityID: 555
Row2: DistrictID: 221; SubDistrictID: 333; CityID: 554
Row3: DistrictID: 221; SubDistrictID: 333; CityID: 557
```

The Rows 0 and 2 have the same city (554) shared across two Subdistricts - 332 and 333.

In this case, the above sql query would double the SUM() value as the same city is counted TWICE.

How am I to solve this complex duplication that logically arose because of this technically faulty, but realistical problem, by not changing the schema of the table? I tried using `distinct`

, but it doesn't fit this purpose, and as such, doesn't work.

You can use `select distinct`

to get one reference to each city in the district, before doing the `join`

:

```
select sum(s.number)
from (select distinct cityid
from state
where destrictid = 21
) c left join
students s
on s.cityid = c.cityid
```