Page 1 of 1

Is it possible to have GROUP BY Functionality in SORT

PostPosted: Wed Jul 27, 2016 9:23 pm
by seethap
Hi,

Just want to know that whether GROUP BY functionality is available in SORT.

Assume that Input File has only two fields. They are NAME (10 bytes) and amount (5 bytes).
Input File:
SURESH 10000
RAMESH 25000
SURESH 15000
SURESH 20000
RAMESH 10000

Output file should have Name, total amount, no of records.

RAMESH 35000 2
SURESH 45000 3

Please let me know sort card to get the output as mentioned above.
Thanks.

Re: Is it possible to have GROUP BY Functionality in SORT

PostPosted: Wed Jul 27, 2016 10:33 pm
by BillyBoyo
Look at OUTFIL reporting features, SECTIONS with TRAILER3 with SUM, TOT/TOTAL, and REMOVECC, NODETAIL.

Re: Is it possible to have GROUP BY Functionality in SORT

PostPosted: Thu Jul 28, 2016 7:31 pm
by NicC
You ask a sort question but post in the JCL section. JCL is not sort - neither DFSort nor SyncSort bith of which have their own section in the forum.

Moved to DFsort - if you have syncsort let us know.

Re: Is it possible to have GROUP BY Functionality in SORT

PostPosted: Thu Jul 28, 2016 11:47 pm
by seethap
Thanks billybobo for the pointer provided.

Please accept my Apologies for posting this in JCL section.

The result yielded with the following sort card.

SORT FIELDS=COPY
OUTFIL FNAME=SORTOUT,REMOVECC,
NODETAIL,
SECTION=(1,10,
TRAILER3=(1,10,' TOTAL AMOUNT ',
TOT=(11,5,ZD,M5,LENGTH=10),
' NO. OF COUNT ',
COUNT=(M10,LENGTH=10)))

Please explain about the significance of using REMOVECC, TO=ZD instead of M5.
Thanks all !!

Re: Is it possible to have GROUP BY Functionality in SORT

PostPosted: Thu Jul 28, 2016 11:49 pm
by seethap
It is SECTIONS not SECTION.

Re: Is it possible to have GROUP BY Functionality in SORT

PostPosted: Fri Jul 29, 2016 11:40 am
by Aki88
Hello,

Your solution is close the original requirement you'd shared in this post; though when you execute the code shared (after correcting SECTIONS), you'll notice that it returns a CC 16 for 'FNAME='; when same is cleaned up, the output generated should look something as below, which is very different from what you were looking for, notice the decimal truncation that has occurred due to usage of M5:


Output from the shared card:

SURESH 100 TOTAL AMOUNT      0.00  NO. OF COUNT          1
RAMESH 250 TOTAL AMOUNT      0.00  NO. OF COUNT          1
SURESH 150 TOTAL AMOUNT      0.00  NO. OF COUNT          1
SURESH 200 TOTAL AMOUNT      0.00  NO. OF COUNT          1
RAMESH 100 TOTAL AMOUNT      0.00  NO. OF COUNT          1

Expected output from original post:

RAMESH 35000 2
SURESH 45000 3
 


I have cleaned up your code a little bit; notice the differences:


//STEP002  EXEC PGM=SORT                                              
//SYSOUT   DD SYSOUT=*                                                
//SORTIN   DD *                                                      
SURESH 10000                                                          
RAMESH 25000                                                          
SURESH 15000                                                          
SURESH 20000                                                          
RAMESH 10000                                                          
/*                                                                    
//SORTOUT  DD SYSOUT=*                                                
//SYSIN    DD *                                                      
* SORT FIELDS=COPY REPLACED WITH VALID SORTING KEY TO GROUP DATA AND  
* FACILITATE SECTIONS PROCESSING.                                    
 SORT FIELDS=(1,6,CH,A)                                              
*                                                                    
* OUTFIL USED TO TAILOR THE OUTPUT WITH REPORTING FEATURES            
* REMOVECC USED TO REMOVE THE CARRIAGE CONTROL                        
* CHARACTER WHICH WILL BE INSERTED FOLLOWING USAGE OF                
* OUTFIL REPORTING FEATURE.                                          
* NODETAIL USED TO AVOID THE INPUT DATA CHUNK FROM                    
* GETTING COPIED TO SORTOUT DS.                                      
* SECTIONS BREAKS THE DATA INTO GROUPS DEFINED IN THE            
* SECTIONS KEY.                                                  
* TRAILER3 COMES BUNDLED WITH SECTIONS.                          
* TOT IS THE SUB-COMMAND OF TRAILER3, ALLOWS                    
* SUMMATION OF USER-DEFINED FIELDS.                              
* ZD IS ZONED DECIMAL FORMAT.                                    
* M10 IS THE EDIT MASK PATTERN, WHICH ALLOWS USER                
* TO CHOOSE HOW THEY WANT TO SEE THE OUTPUT DATA.                
* COUNT IS ANOTHER SUB-PARAMETER, WHICH DOES WHAT                
* THE NAME SAYS: IT COUNTS.                                      
 OUTFIL REMOVECC,NODETAIL,                                      
 SECTIONS=(1,6,                                                  
 TRAILER3=(1,6,                                                  
           C' TOTAL AMOUNT:',TOT=(8,5,ZD,M10,LENGTH=10),        
           C' COUNT:',COUNT=(M10,LENGTH=9)))                    
/*                                                              
 


Returns:


RAMESH TOTAL AMOUNT:     35000 COUNT:        2  
SURESH TOTAL AMOUNT:     45000 COUNT:        3  
 


The above code is more or less self-explanatory, for more details on edit mask patterns, data formats, refer DFSORT Application Programming Guide.

Hope this helps.