838.TXT
     =====================================================================
     Hot Tip: Grand Total and Sub-Total in One Command
     =====================================================================
     PRODUCT:  R:BASE                   VERSION:  5.5 or Higher
     =====================================================================
     CATALOG: Programmin in R:BASE      AREA   :  Data Manipulation
     =====================================================================
 
     The GROUP BY option of the SELECT command is used to calculate
     sub-totals by group. There is no SQL option, however, that
     calculates the grand total at the same time. To do both sub-totals
     and grand totals in a single SELECT command, use an R:BASE SELECT
     command option (not part of the ANSI standard) that calculates a
     column total. The "=S" option to the right of a column name or
     expression indicates to R:BASE to sum the column and display a grand
     total. For example, using the sample database CONCOMP, the following
     command displays a list of customers with a sub-total by customer,
     and a grand total for all customers.
 
     SELECT custid, company, SUM(invoicetotal)=S +
     FROM transmaster, customer +
     WHERE customer.custid = transmaster.custid +
     GROUP BY custid, company
 
      custid     company                            SUM(invoicetota
      ---------- ---------------------------------- ---------------
             100 PC Distribution Inc.               $116,737.00
             101 Computer Distributors Inc.         $376,395.00
             102 Industrial Computers Inc.          $201,425.87
             103 Computer Mountain Inc.             $211,926.75
             104 Industrial Concepts Inc.           $234,292.25
             105 PC Consultation and Design         $193,379.75
             106 Computer Warehouse                 $153,067.30
             107 Midtown Computer Co.               $199,653.00
      ---------- ---------------------------------- ---------------
 
                                                    $1,686,876.92