Updated June 16, 2000
Account codes consist of several fields that capture attributes of transactions entered into the accounting system. For example, consider the following simple code structure that captures information on whether an item is an expense or revenue, the type of expense or revenue, the responsible individual, the geographic region, and the dollar amount of the transaction.
| Exp. or Rev. | Type | Responsibility | Region | Amount |
| 06 06 07 07 06 |
400 500 900 900 300 |
05 06 08 08 02 |
25 35 40 40 45 |
2.657.55 5,325.54 22,435.63 44,258.98 2,356.20 |
Consider the transactions in the above table. A manager can extract total sales by summing the amounts in the rows with a 07 in the first field. This action will provide a total of sales of $66,712.59. If a manager wants to know who approved the expense listed on the first row, the manager merely needs to contact the individual with the code of 05.
With the few transactions in this example a manager can easily compute the amounts for any code. However, a real company will have thousands of transactions, so the manager must use database software to manage the transactions. The following examples from the database at the Owen school illustrate how you can use SQL commands to extract relevant data from a large database.
This database represents actual transactions from a real company. Selected features of the data have been disguised to protect the company identity, but the transactions are real.
We have four files available for student use in the Oracle database. These files are: CUSTMAST, CREDIT, SALESVAR, and ARSALES. CUSTMAST includes information on each customer like phone numbers and address. CREDIT includes information on the credit record for customers for the last month of the year. The SALESVAR file includes budgeted and actual sales data for about 200 products. Finally, the ARSALES file contains approximately 32,000 invoice lines of data for actual sales for the year.
In the following discussion I will review SQL commands that you can use to extract data from these files.
First, to see what kinds of fields a table contains, you can use the DESCRIBE command. This command lists all the columns for the specified table. For example, you get these results from using the DESCRIBE command with the CUSTMAST table.
ALWAYS REMEMBER TO PUT A SEMICOLON AT THE END OF THE ORACLE COMMAND. IT GETS MAD AT YOU IF YOU OMIT THE SEMICOLON.
SQL> describe custmast; Name Null? Type ------------------------------- -------- ---- RECCDE CHAR(9) STATCD CHAR(9) CUSTNO NOT NULL CHAR(9) CUSNAM CHAR(30) CUSAD1 CHAR(27) CUSAD2 CHAR(26) CUSAD3 CHAR(28) CUSCTY CHAR(19) CUSSTA CHAR(14) CUSZIP CHAR(11) CUSZSX CHAR(10) AREACD NUMBER(9) PHONE1 NUMBER(13) PHONE2 NUMBER(12) PHNEXT CHAR(21) FXAREA CHAR(13) FXPHN1 CHAR(18)
I cut off the output here to avoid filling the screen with information you do not need. You can refer to these column names when you want to extract information from a table.
For example, you can look at the customer numbers in the CUSTMAST file by using the SELECT command like this.
SQL> select custno from custmast; CUSTNO --------- 005944 008000 012100 012550 015491 030320 031720 036200 036204 036205 036208 036210 036220 036225 036230 044130
I cut off the output again so you would not have to look at all 165 customer numbers.
The next command gives you some idea of the names of the columns in the ARSALES table.
SQL> describe arsales; Name Null? Type ------------------------------- -------- ---- RECCDE CHAR(2) STATCD CHAR(1) DTLRRN NUMBER(10) INVTYP NUMBER(3) CUSTNO NUMBER(8) BILLTO NUMBER(8) ORDNO NUMBER(8) BACKNO NUMBER(3) ORDDIV CHAR(2) ORDTYP CHAR(1) SLSDIV CHAR(2) SLSMAN CHAR(3) OPERID CHAR(3) SHPYR CHAR(2) SHPMO CHAR(2) SHPDA CHAR(2) INVYR CHAR(2) INVMO CHAR(2) INVDA CHAR(2) COMRAT NUMBER(9,4) COMRT2 NUMBER(9,4) SLSDV2 CHAR(2) SLSMN2 CHAR(3) WHSECD CHAR(5) CUSTWH CHAR(5) BLADNG CHAR(6) INVNO NUMBER(7) SHPSTA CHAR(2)
I cut this one off here to keep you from having to look at all the names.
To find out total dollar sales for customer 533011, you issue the following command. EXTPRC is the name for the column that holds the value derived from multiplying units sold by unit selling price.
SQL> select sum(extprc) from arsales where custno=533011; SUM(EXTPRC) ----------- 93822.43
The response tells you this customer generated $93,822.43 in sales last year. You could issue the same command for any customer to determine their total sales.
To learn how much margin a customer generated, you can deduct the total cost of the units sold from the dollar sales. The word EXTCST describes the column holding the value of units sold multiplied by unit cost. The following example illustrates how to do this.
SQL> select sum(extprc)-sum(extcst) from arsales where custno=533011; (SUM(EXTPRC))-(SUM(EXTCST)) --------------------------- 26691.32
To verify the calculation, deduct the cost derived from the following command from the revenue computed above.
SQL> select sum(extcst) from arsales where custno=533011; SUM(EXTCST) ----------- 67131.11
To see total dollar sales by month, use this command. ACCTMO defines the column in which the month appears, and the GROUP BY command tells the database to group the results by the month of sale.
SQL> select acctmo, sum(extprc) from arsales where custno=533011 group by acctmo; AC SUM(EXTPRC) -- ----------- 01 0 02 0 03 40761.84 04 788.6 05 24449.55 06 27070.23 07 240.52 08 113.14 09 59.33 10 339.22 12 0 11 rows selected.
To compute the total sales for a specific product you must follow these rules:
Notice how I used these two rules in the following example.
SQL> select sum(extprc) from arsales where itemno='TP28561A'; SUM(EXTPRC) ----------- 188804.47
Here is how you can see what product numbers a customer is buying from you. Use the UNIQUE command to get the database to list only one product number for each product sold to the customer.
SQL> select unique(itemno) from arsales where custno=605899; ITEMNO --------------- 054565L 054878L 056782P 078416L 078696L 10005ZZMLTYLL 27424CD98L 309232L 309239L 309246L 309263L 309266L 309271DL 439060L 439065L 447117L 447152DL 447158L 447180L 447204L 447238L
I stopped the output here to avoid burdening you with lots of stuff.
Here is another example of how you can generate dollar margin; this case computes the dollar margin generated by sales of item E83TP to customer 605899.
SQL> select sum(extprc)-sum(extcst) from arsales where custno=605899 and itemno= 'E83TP'; SUM(EXTPRC)-SUM(EXTCST) ----------------------- 10203.52
You can verify this calculation from the following two numbers.
SQL> select sum(extprc) from arsales where custno=605899 and itemno='E83TP'; SUM(EXTPRC) ----------- 28046.54 SQL> select sum(extcst) from arsales where custno=605899 and itemno='E83TP'; SUM(EXTCST) ----------- 17843.02
If you want to know the numbers of the salespeople working for this company, issue the following command.
SQL> select unique(slsman) from arsales; SLS --- 013 114 117 215 399 615 616 662 675 678 681 683 691 13 rows selected.
Or, you can just issue this command to find out how many salesman the company has.
SQL> select count(unique(slsman)) from arsales; COUNT(UNIQUE(SLSMAN)) --------------------- 13
Notice how I combined the COUNT and the UNIQUE commands to compute how many salespeople this company has.
I can issue this command to find out how many dollar sales salesman 678 generated for the years.
SQL> select sum(extprc) from arsales where slsman=678; SUM(EXTPRC) ----------- 917386.88
And, I can issue this command to see the total dollar sales this salesperson generated each month.
SQL> select sum(extprc) from arsales where slsman=678 group by
acctmo;
SUM(EXTPRC)
-----------
101123.48
250216.58
130027.39
7596.03
17558.95
66765.86
51713.66
51119.69
16332.76
16239.27
1095.36
207597.85
12 rows selected.
If you want to see total sales by invoice for customer 533011, then you can issue this command.
SQL> select invno,sum(extprc) from arsales where custno=533011
group by invno;
INVNO SUM(EXTPRC)
--------- -----------
1221 17.57
2683 19.41
2710 149.81
3917 33.36
4438 29.46
4996 91.13
4997 248.09
521363 0
527217 0
530086 34655.61
530087 3601
530478 257
530479 2164.34
531553 66.32
533299 587.91
533704 114.34
534425 0
535680 86.35
536381 0
536382 0
536383 0
I cut off the output here.
To find out the sales for March for customer 533011, just issue this command.
SQL> select sum(extprc) from arsales where custno=533011 and acctmo=3; SUM(EXTPRC) ----------- 40761.84 And this command shows how much margin this customer generated from March sales.
SQL> select sum(extprc)-sum(extcst) from arsales where custno=533011 and acctmo=3; SUM(EXTPRC)-SUM(EXTCST) ----------------------- 13419.82
SQL> select max(extprc) from arsales where custno=030320;
MAX(EXTPRC)
-----------
5862.5
SQL> select min(extprc) from arsales where custno=030320;
MIN(EXTPRC)
-----------
0
SQL> select min(extprc), max(extprc), avg(extprc), stddev(extprc),
variance(extprc) from arsales;
MIN(EXTPRC) MAX(EXTPRC) AVG(EXTPRC) STDDEV(EXTPRC) VARIANCE(EXTPRC)
----------- ----------- ----------- -------------- ----------------
0 95440.8 458.94536 2542.1588 6462571.5
SQL> select unique(majter) from arsales; MAJ --- 013 212 664
SQL> select unique(slsman) from arsales; SLS --- 013 114 117 215 399 615 616 662 675 678 681 683 691 13 rows selected.
SQL> select count(slsman) from arsales where slsman>0; COUNT(SLSMAN) ------------- 32271
SQL> select count (unique(slsman)) from arsales where slsman>0; COUNT(UNIQUE(SLSMAN)) --------------------- 13
Notice how I use the name margin to label the arithmetic
expression that computes
the margin in this next command. You can use names like this to label output or
to serve as variable names for additional manipulation. See the next example to see how
this works.
SQL> select slsman, sum(extprc)-sum(extcst) margin from arsales group by slsman;
SLS MARGIN --- --------- 013 879588.61 114 2249319.9 117 18251.22 215 43923.84 399 71761.93 615 301110.84 616 6654.38 662 90663.8 675 397053.13 678 326217.41 681 286821.85 683 34161.46 691 73787.42 13 rows selected.
SQL> select slsman,sum(extprc)- sum(extcst)margin from arsales group by slsman order by margin; SLS MARGIN --- --------- 616 6654.38 117 18251.22 683 34161.46 215 43923.84 399 71761.93 691 73787.42 662 90663.8 681 286821.85 615 301110.84 678 326217.41 675 397053.13 013 879588.61 114 2249319.9 13 rows selected.
Notice how the output was sorted by the size of the margin.