Pass-thru SQL - counting and grouping

Pass-thru SQL - counting and grouping

Post by Alistair Gord » Sat, 09 Jun 2001 22:13:01

I have some code that groups and summarizes data on a DB2 table and then counts
the number of rows in total set of summarized data.  Step is coded as follows:

proc sql noprint;                          
  connect to db2 (ssid=&ssid);            
  execute (set current sqlid=&boxp) by db2;
  select kount6 into: kount6 from                    
 (select count(*) as kount6 from connection to db2
    (select fund_id_i                              
    from &db2auth..t32_box_l2_txn_MLY              
    where prtn_no_n=&l2_txn                        
      and systm_d > &db2ldate                      
    group by systm_d                              
    for fetch only))
  %put &sqlxmsg;      
  disconnect from db2;

This works but is essentially a two step process: DB2 summarizes the data and
then passes the data across the connection to SAS where the number of rows
are counted.  

Is it possible to do it all on the DB2 side i.e. summarize and count such that
only the total is passed back ?  Is it also possible to group and count data
without having to provide a summary function ?

I have had a few stabs at this but am only able to get DB2 to return the number
of rows in the last group processed.

Many thanks.