php - sum of cases in grand-parent, parents, children,, and etc

Answer

| id | parent_id | city_name |

Write your answer

Answer

|||- Region //[its parent_id = 0] -- District //[its parent_id = the region id] ---- Parent-city //[its parent_id = the district id] ------ Child-city //[its parent_id = the parent-city id]|||

Write your answer

Answer

--- | id | city_id | date | n_cases | r_cases | d_cases |

Write your answer

Answer

---|||[covid19cities]|||n_cases|||r_cases|||d_cases|||SELECT sum(`n_cases`) AS city_n_cases, cities.name AS city_name, cities.id AS city_id, FROM covid19cities INNER JOIN cities ON cities.id = covid19cities.city_id WHERE covid19cities.city_id = '#'|||p.s. sorry for my English :/

Write your answer

Answer

Solution:

Consider the following base query, that gives you the sum of each category of cases percity_id. We can get that information by looking atcovid19cities only:

select 
    cvc.city_id,
    sum(cvc.n_cases) sum_n_cases,
    sum(cvc.r_cases) sum_r_cases,
    sum(cvc.d_cases) sum_d_cases
from covid19cities cvc
group by cvc.city_id
Copy code

Now let's bring thecities table. This gives the same results, and you can display the city name too:

select 
    c.id   city_id,
    c.name city_name, 
    sum(cvc.n_cases) sum_n_cases,
    sum(cvc.r_cases) sum_r_cases,
    sum(cvc.d_cases) sum_d_cases
from covid19cities cvc
inner join cities c on c.id = cvc.city_id
group by c.id, c.name
Copy code

Starting from there, we can follow the hierarchy upwards, level by level, by adding more joins on thecities table and changing the columns in theselect andgroup by clauses.

Let's get the number of cases per parent city: we join thecities table a second time, with aliaspc (for parent city):

select 
    pc.id   parent_city_id,
    pc.name parent_city_name, 
    sum(cvc.n_cases) sum_n_cases,
    sum(cvc.r_cases) sum_r_cases,
    sum(cvc.d_cases) sum_d_cases
from covid19cities cvc
inner join cities c  on c.id  = cvc.city_id
inner join cities pc on pc.id = c.parent_id
group by pc.id, pc.name
Copy code

The next level is the district:

select 
    d.id   distict_id,
    d.name district_name, 
    sum(cvc.n_cases) sum_n_cases,
    sum(cvc.r_cases) sum_r_cases,
    sum(cvc.d_cases) sum_d_cases
from covid19cities cvc
inner join cities c  on c.id  = cvc.city_id
inner join cities pc on pc.id =  c.parent_id
inner join cities d  on d.id  = pc.parent_id
group by d.id, d.name
Copy code

Finally, here is the query that gives the information at the upper level, that is the region:

select 
    r.id   region_id,
    r.name region_name, 
    sum(cvc.n_cases) sum_n_cases,
    sum(cvc.r_cases) sum_r_cases,
    sum(cvc.d_cases) sum_d_cases
from covid19cities cvc
inner join cities c  on c.id  = cvc.city_id
inner join cities pc on pc.id =  c.parent_id
inner join cities d  on d.id  = pc.parent_id
inner join cities r  on r.id  =  d.parent_id
group by r.id, r.name
Copy code

---

As a more general thought: note that the complexity here comes from the fact that you are storing hierachical structure in thecities table. It would be much simpler to have separated tables to store each entity, with foreign keys to represent the relationships, like:

regions:        region_id, region_name
districts:      district_id, district_name, region_id
parent_cities:  parent_city_id, parent_city_name, district_id
cities:         city_id, city_name, parent_city_id
Copy code

With this design, your last query would look like:

select 
    r.region_id,
    r.region_name, 
    sum(cvc.n_cases) sum_n_cases,
    sum(cvc.r_cases) sum_r_cases,
    sum(cvc.d_cases) sum_d_cases
from covid19cities cvc
inner join cities c         on c.id              = cvc.city_id
inner join parent_cities pc on pc.parent_city_id =  c.parent_city_id
inner join districts     d  on d.district_id     = pc.district_id
inner join regions       r  on r.region_id       =  d.region_id
group by r.region_id, r.region_name
Copy code

That's the same number of joins, but things are stored in different tables so the query is much easier to write and read.

Write your answer

Answer

Solution:

Since there are only 3 levels, not an arbitrary number of levels, I suggest that you are working too hard. Have a single table with 3 columns for the region, district, and city spelled out in the table. That's only a few thousand rows per day, so the lack of normalization will not lead to a huge disk expense.

If, on the other hand, this is a learning exercise, then get MySQL 8 or MariaDB 10.2 and learn about "Recursive CTEs".

Write your answer

Ask a Question