Hello! Tell me, please, is it possible in one request with two nested groupings to immediately receive the number of records in each external and each internal grouping? For example, for a table with fields:

Name of resident, district, city

get one result of the form:

Name of resident, district, city, number of inhabitants in this area, number of inhabitants in this city.

  • 2
    See dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html , ROLLUP modifier. Alternatively, select a.FIO, a.REGION, a.CITY, b.REGION_CNT, c.CITY_CNT from tab a join (select REGION, CITY, count ( ) REGION_CNT from tab group by REGION, CITY) b on a.REGION = b.REGION and a.CITY = b.CITY join (select CITY, count ( ) CITY_CNT from tab group by CITY ) c on a.CITY = c.CITY; - alexlz
  • Thank you very much. - masha2

1 answer 1

response from comment:

Check out the documentation for the ROLLUP modifier .

Differently:

select a.FIO, a.REGION, a.CITY, b.REGION_CNT, c.CITY_CNT from tab a join ( select REGION, CITY, count() REGION_CNT from tab group by REGION, CITY ) b on a.REGION = b.REGION and a.CITY = b.CITY join ( select CITY, count() CITY_CNT from tab group by CITY ) c on a.CITY = c.CITY;