Databases Reference
In-Depth Information
Restructure the salesperson commission program as an interim measure
while the new program is finalized. Reset everyone's commission to an
average of all current commissions.
FOR EXAMPLE
Combining Keywords
One of the real strengths of the SELECT queries lies in what you can do by
combining keywords in different ways to get the exact results that you want.
GROUP BY, for example, is useful in generating all types of summary result
reports. For example, you might want to generate a report that provides a
daily sales report by salesperson over a month. You would use the GROUP
BY clause with two grouping conditions, grouping first by day and then by
salesperson. Suppose you have an ORDER_HEAD table from which you can
get this information. You might use:
SELECT SPNUM, SPNAME, SUM(ORDER_TOT)
FROM ORDER_HEAD
GROUP BY DAY_DATE, SPNUM, SPNAME
This would return a result grouped first by day, and then within that group,
by salesperson. The SPNUM and SPNAME groupings are identical. The rea-
son you need to have both in the GROUP BY clause is so that you can have
both in the SELECT list. Otherwise, either one could be used by itself to
define the grouping. This particular query, however, might not give you the
information in the format in which you need it. It would probably be eas-
ier to review the result if you return the result in SPNUM order, by adding
an ORDER BY clause, as in:
SELECT SPNUM, SPNAME, SUM(ORDER_TOT)
FROM ORDER_HEAD
GROUP BY DAY_DATE, SPNUM, SPNAME
ORDER BY SPNUM
In this situation, you couldn't order the result by SPNAME. Why not?
Because it isn't an aggregate and it doesn't appear in the GROUP BY clause.
If you wanted, to sort by SPNAME, you might change the query to read:
SELECT SPNUM, SPNAME, SUM(ORDER_TOT)
FROM ORDER_HEAD
GROUP BY DAY_DATE, SPNAME, SPNUM
ORDER BY SPNAME
