I keep getting the same number outputted for the Total Sales, Minimum Sale, Largest Sale and Average Sale.
The Total Invoices is working perfectly, but I cant seem to figure out how to fix the other ones.
Here's the query:
SELECT SUM( b.`Number of Invoices`) AS `Total Invoices`,
SUM( b.`Total Customer Purchases`) AS `Total Sales`,
MIN( b.`Total Customer Purchases`) AS `Minimum Sale`,
MAX( b.`Total Customer Purchases`) AS `Largest Sale`,
AVG( b.`Total Customer Purchases`) AS `Average Sale`
FROM (SELECT a.CUS_CODE,
COUNT(a.`Number of Invoices`) AS `Number of Invoices`,
SUM(a.`Invoice Total`) AS `Total Customer Purchases`
FROM ( SELECT CUS_CODE,
LINE.INV_NUMBER AS `Number of Invoices`,
SUM(LINE.LINE_UNITS * LINE.LINE_PRICE) AS `Invoice Total`
FROM `ttriggs`.`INVOICE`, `ttriggs`.`LINE`
WHERE INVOICE.INV_NUMBER = LINE.INV_NUMBER
GROUP BY CUS_CODE, LINE.INV_NUMBER
) a
) b
GROUP BY b.CUS_CODE;
Heres the database diagram
https://www.dropbox.com/s/b8cy5l29jwh8lyv/1_edit.jpg
Subquery generates:
CUS_CODE 10011
Number of Invoices 8
Total Customer Purchases 1119.03
Any help is greatly appreciated,
Thanks!
