823.TXT
=====================================================================
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
$200,000.
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.