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.
Group the subtables: SQL vs. esProc, for example:
To list the employee and count the cities WHERE the employee has worked over one year.
Database table: staff, resume.
And their main fields:
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
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:
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