Question

Write a query to produce the number of invoices and the total purchase amounts by customer, using the output shown in Figure P7.18 as your guide. Note the results are sorted by customer code. (Compare this summary to the results shown in Problem 17.)

Answer 1

Query Solution:

SELECT CUS_CODE,

COUNT(INV_NUMBER) AS 'Number of Invoices',

AVG(INV_TOT) AS 'Average Invoice Amount',

MAX(INV_TOT) AS 'Max Invoice Amount',

MIN(INV_TOT) AS 'Min Invoice Amount',

SUM(INV_TOT) AS 'Total Customer Purchases'

FROM (SELECT CUS_CODE, L.INV_NUMBER AS INV_NUMBER,

SUM(L.LINE_UNITS*L.LINE_PRICE) AS INV_TOT

FROM INVOICE I, LINE L

WHERE I.INV_NUMBER = L.INV_NUMBER

GROUP BY CUS_CODE, L.INV_NUMBER) AS IL

GROUP BY CUS_CODE ;