Calculate Total Sales by Month
     PRODUCT:   R:BASE                 VERSION:  4.5 or Higher
     CATALOG:   Programming in R:BASE  AREA   :  Logic & Data Manipulation
     Sometimes, a question can't be answered by a single SELECT command.
     In these situations, views on views are used to quickly return an
     answer. Don't spend hours trying to use a single command when using
     an intermediate view can make the process simple and fast.
     Grouping data by month is one situation where an intermediate view
     makes the query simpler. It's easy to find the month from a DATE data
     type column using the IMON SuperMath function, but you can't group by
     a function, only a column. So even though you can extract the month,
     you still need to group the data by the actual date, which groups the
     data by day, not by month. Instead, create a view that includes all
     the relevant columns and instead of the date column, includes just
     the month. Then the final query, grouping the data by month, is done
     using the view instead of the actual table.
     For example, you want to create a report listing companies with total
     sales of over $200,000 in one month. The table storing order
     information, Transmaster, contains a date column, transdate, but not
     a separate column for the month. The following steps show how to
     create the view and select the desired data.
     1. Create a view that lists the customer number, the month, and the
     sales amount for each transaction. Other columns could be included in
     the view. The view is created using optional column names since we are
     selecting an expression.
     CREATE VIEW sales (custid, month, amount) AS +
     SELECT custid, IMON(transdate), invoicetotal +
     FROM transmaster
     The IMON function returns the integer representation of the month
     (January = 1). You could also use the TMON function which returns a
     3-character text representation of the month (January = Jan).
     2. Select from the view to verify the data.
     SELECT * FROM sales ORDER BY month, custid
     custid     month      amount
     ---------- ---------- ---------------
            100          7      $29,457.00
            100          7      $87,280.00
            101          7      $95,462.50
            101          7      $32,730.00
            102          7      $31,775.37
            103          7     $166,104.75
            103          7      $45,822.00
            104          7      $68,733.00
            105          7     $182,469.75
            105          7      $10,910.00
            106          7     $153,067.30
            101          8      $24,547.50
            101          8     $192,016.00
            101          8      $31,639.00
            102          8     $169,650.50
            104          8      $61,368.75
            104          8     $104,190.50
            107          8     $118,646.25
            107          8      $81,006.75
     Each customer sale is now associated with just the month of the sale,
     not the year and day.
     3. Use a GROUP BY clause to see the total sales by customer by month.
     Some customers have sales in two months, some customers have sales in
     just one month.
     SELECT custid, month, SUM(amount) FROM sales GROUP BY custid, month
      custid     month      SUM (amount)
      ---------- ---------- ---------------
             100          7     $116,737.00
             101          7     $128,192.50
             101          8     $248,202.50
             102          7      $31,775.37
             102          8     $169,650.50
             103          7     $211,926.75
             104          7      $68,733.00
             104          8     $165,559.25
             105          7     $193,379.75
             106          7     $153,067.30
             107          8     $199,653.00
     4. The HAVING clause is then added to the command to restrict the
     results to those customers whose sales for a single month are over
     SELECT custid, month, SUM(amount) FROM sales GROUP BY custid, month
     HAVING SUM(amount) > $200000
      custid     month      SUM (amount)
      ---------- ---------- ---------------
             101          8     $248,202.50
             103          7     $211,926.75
     The HAVING clause is used to qualify groups of rows. Because it looks
     at a group of rows, the aggregate functions can be used to put
     conditions on the group. This SELECT command lists the customer ID,
     the month, and the total sales for each customer, but only if the
     total sales are over $200,000.00 for one month.
     5. To see the company name in addition to the data in the view, create
     a multi-table SELECT command using the view and the customer table.
     SELECT custid, MAX(company), month, SUM(amount) FROM sales, customer
     WHERE sales.custid = customer.custid GROUP BY custid, month HAVING
     SUM(amount) > $200000
      custid     MAX (company)             month     SUM (amount)
      ---------- ------------------------- ------- --------------
             101 Computer Distributors Inc.      8    $248,202.50
             103 Computer Mountain Inc.          7    $211,926.75
     We use the aggregate function MAX on the company column so it doesn't
     need to be added to the GROUP BY clause. It doesn't change the
     results because there is only one company name for each customer ID.
     The query can also be used as a sub-SELECT in a WHERE clause. You can
     think of the query as returning a list of customer ID's. This list of
     customer ID's can then be compared to the customer ID column in other
     tables. You can use the sub-SELECT with an EDIT USING or a PRINT
     command as well as with another SELECT command.
     SELECT custid, company, transid, transdate, invoicetotal+
     FROM customer t1, Transmaster t2
     WHERE custid IN (SELECT custid FROM sales GROUP BY custid,+
     month HAVING SUM(amount) > $200000) ORDER BY custid
     EDIT USING tranform WHERE custid IN (SELECT custid FROM+
     sales GROUP BY custid, month HAVING SUM(amount) > $200000)+
     ORDER BY custid
     PRINT goodcust WHERE custid IN (SELECT custid FROM sales+
     GROUP BY custid, month HAVING SUM(amount) > $200000)
     Don't limit yourself by thinking you can only use one SELECT
     command. With views you can combine SELECT commands to return a
     variety of results. Views do not limit your data, but rather expand
     the possibilities.