Grouping the subtable

List the names of the employees and count the cities where each employee has worked for over one year.
    SELECT name,count(*) city_total
    FROM (SELECT name, city
          FROM employee,resume WHERE
          GROUP BY name,city
          HAVING sum(work_days)>=365)
    GROUP BY name

Process the subtable through the multi-table join. The grouped result set has the same number of records as the subtable has. It must be grouped again in order to join the records to have the same number as the main table has.

1 =demo.query(“select * from employee”).new(name,>=365):city_total)

esProc handles the sets of the subtable as the fields of the main table, hence groups and filters them as a regular set.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.