Write a script with the necessary formatting commands (e.g. COLUMN, BREAK, TTITLE, etc.) and an SQL statement to create a report that lists each customer number, each order number he/she placed, the total value of each order, the sum of the total values of all the orders for each customer, and the grand total of all the orders placed by all customers under the following heading:

Question

Orders Total Values

Page: xx
Customer Order Total
No No Value

Given the following schema of a database table:
Orders(orderNo, dateDue, totalValue, status, custNo)
where: custNo is the number of the customer who placed the order.

Answer 1

COLUMN custNo FORMAT A10 HEADING 'Customer | No'
COLUMN orderNo FORMAT A8 HEADING 'Order | No'
COLUMN totalValue FORMAT L99999.99 HEADING 'Total | Value'
SET UNDERLINE =
BREAK ON custNo SKIP 1 ON Report skip 2
COMPUTE SUM OF totalValue on custNo
COMPUTE SUM OF totalValue on Report
TTITLE CENTER 'Orders Total Value' SKIP 1 -
Right ' Page: ' FORMAT 999 sql.pno SKIP1
SELECT custNo, orderNo, totalValue
FROM Orders
ORDER BY custNo, orderNo;