Group the subtables: SQL vs. esProc

We often need to group the subtables during the business analytics and statistics. We know it is easy for us to group a parent table, but not that convenient to group the subtables. What your way to group a subtable? Here let's see how SQL and esProc group the subtables.

For example:

To list the employee and count the cities WHERE the employee has been working over one year.

Database table: staff, resume.

And their main fields:

Staff: name

Resume: name,city,workingDays

Check the SQL solution:

SELECT name,count( *) cityCount

FROM (SELECT staff.name name,resume.city city

FROM staff,resume WHERE staff.name=resume.name

GROUP BY name,city

HAVING sum(workingDays)>=365)

GROUP BY name

Process the subtable in the way as joining the multiple tables. The grouped result set has the same number of records as the subtable. The result sets must be grouped again in order to join the records to have the same number as the primary table.

Check the esProc solution:

A

1 =staff.new(name,resume.group(city).count(~.sum(workingDays)>=365):cityCount)

Handle the subtable sets as the fields of the primary table, hence group and filter them as a regular set.

So any other way to group the subtable? Welcome to discuss it with me!

Author: Jim King

BI technology consultant for Raqsoft

10 + years of experience on BI/OLAP application, statistical computing and analytics

Email: [email protected]

Website: www.raqsoft.com

Blog: datakeyword.blogspot.com/

 

[Dhruv Bindhani]

We often need to group the subtables during the business analytics and statistics. We know it is easy for us to group a parent table, but not that convenient to group the subtables. What your way to group a subtable? Here let's see how SQL and esProc group the subtables.

For example:

To list the employee and count the cities WHERE the employee has been working over one year.

Database table: staff, resume.

And their main fields:

Staff: name

Resume: name,city,workingDays

Check the SQL solution:

SELECT name,count( *) cityCount

FROM (SELECT staff.name name,resume.city city

FROM staff,resume WHERE staff.name=resume.name

GROUP BY name,city

HAVING sum(workingDays)>=365)

GROUP BY name

Process the subtable in the way as joining the multiple tables. The grouped result set has the same number of records as the subtable. The result sets must be grouped again in order to join the records to have the same number as the primary table.

Check the esProc solution:

A

1 =staff.new(name,resume.group(city).count(~.sum(workingDays)>=365):cityCount)

Handle the subtable sets as the fields of the primary table, hence group and filter them as a regular set.

So any other way to group the subtable? Welcome to discuss it with me!

Author: Jim King

BI technology consultant for Raqsoft

10 + years of experience on BI/OLAP application, statistical computing and analytics

Email: [email protected]

Website: www.raqsoft.com

Blog: datakeyword.blogspot.com/

Brief Review: Subtable Grouping in SQL vs esProc


Grouping subtables—nested or related records—can be complex in SQL, especially when aggregating data tied to a primary table. For instance, counting cities where each employee has worked over a year involves nested grouping and filtering across staff and resume tables.


In SQL, this requires:

• A subquery joining and grouping by name and city.
• A HAVING clause to filter by workingDays >= 365.
• An outer query to count qualifying cities per employee.

SELECT name, COUNT(*) cityCount <br>FROM (<br> SELECT staff.name, resume.city <br> FROM staff, resume <br> WHERE staff.name = resume.name <br> GROUP BY name, city <br> HAVING SUM(workingDays) &gt;= 365<br>) <br>GROUP BY name;<br>

In esProc, subtable processing is simplified by treating subtables as fields within the main table:


=staff.new(name, resume.group(city).count(~.sum(workingDays)&gt;=365):cityCount)<br>

This approach offers a declarative and intuitive method to group and filter subtables directly, avoiding the complexity of nested SQL queries.


Conclusion: esProc provides a more straightforward, field-centric way to handle subtables, while SQL requires careful structuring and multiple groupings.