SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Group the subtable by SQL and esProc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

datakeyword
Starting Member

10 Posts

Posted - 11/07/2012 :  00:11:06  Show Profile  Reply with Quote
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 worked 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

edit: moved to proper forum

Edited by - datakeyword on 11/07/2012 00:19:51

AnalystBrown
Starting Member

7 Posts

Posted - 11/08/2012 :  00:16:31  Show Profile  Reply with Quote
It seems that both solutions works fine with the same problems. It's just the statement is different. Except a shorter and simple statement, I don't see other advantages of your product. Why do you compare these two tools?
Go to Top of Page

datakeyword
Starting Member

10 Posts

Posted - 11/08/2012 :  01:18:45  Show Profile  Reply with Quote
To make it easier, which will u choose? U don't need other advantages except easier and quicker. R is great, esProc is no bad too, right?

Jim King
BI technology consultant for Raqsoft
10 + years of experience on BI/OLAP application, statistical computing and analytics
Email: Contact@raqsoft.com
Website: www.raqsoft.com
Blog: datakeyword.blogspot.com/
Go to Top of Page

garyjohn
Starting Member

7 Posts

Posted - 11/08/2012 :  02:53:19  Show Profile  Reply with Quote
Good tips! Both solutions are quite OK! But I find esProc is more suitable for business personell without too high IT technology, like marketing and sales professional, HR managers, or financial experts, esProc is good choice for them. While for upper level scientific research or big data analytics, R can not be replaced. Am I right, Jim?

business analyst, marketing director
Go to Top of Page

datakeyword
Starting Member

10 Posts

Posted - 11/16/2012 :  03:26:53  Show Profile  Reply with Quote
garyjohn, R is great, and just as you mentioned, it is not replaceable. But, every tool has its advantages and disadvantages, for exampel,r is famous for big data, esProc is outstanding for it's excell-style interface, and strong capability for handle complex structured data.

Jim King
BI technology consultant for Raqsoft
10 + years of experience on BI/OLAP application, statistical computing and analytics
Email: Contact@raqsoft.com
Website: www.raqsoft.com
Blog: datakeyword.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000