USE SQL_INVOICING;
SELECT
Invoice_ID,
Invoice_Total,
(SELECT AVG(Invoice_Total) FROM Invoices) AS Invoice_Average,
Invoice_Total - (SELECT AVG(Invoice_Total) FROM Invoices) AS Difference
FROM Invoices;
| Invoice_ID | Invoice_Total | Invoice_Average | Difference |
|---|---|---|---|
| 1 | 101.79 | 152.388235 | -50.598235 |
| 2 | 175.32 | 152.388235 | 22.931765 |
| 3 | 147.99 | 152.388235 | -4.398235 |
| 4 | 152.21 | 152.388235 | -0.178235 |
| 5 | 169.36 | 152.388235 | 16.971765 |
| 6 | 157.78 | 152.388235 | 5.391765 |
| 7 | 133.87 | 152.388235 | -18.518235 |
| 8 | 189.12 | 152.388235 | 36.731765 |
| 9 | 172.17 | 152.388235 | 19.781765 |
| 10 | 159.50 | 152.388235 | 7.111765 |
| 11 | 126.15 | 152.388235 | -26.238235 |
| 12 | 135.01 | 152.388235 | -17.378235 |
| 13 | 167.29 | 152.388235 | 14.901765 |
| 14 | 162.02 | 152.388235 | 9.631765 |
| 15 | 126.38 | 152.388235 | -26.008235 |
| 16 | 180.17 | 152.388235 | 27.781765 |
| 17 | 134.47 | 152.388235 | -17.918235 |
Aggregate
SELECT
I.Client_ID,
C.Client,
SUM(Invoice_Total) Client_Invoice_Total,
Avg(Invoice_Total) Client_Average_Invoice
FROM Invoices I
INNER JOIN Clients C ON
I.Client_ID = C.Client_ID
GROUP BY I.Client_ID, C.Client;
| Client_ID | Client | Client_Invoice_Total | Client_Average_Invoice |
|---|---|---|---|
| 1 | Vinte | 802.89 | 160.578000 |
| 2 | Myworks | 101.79 | 101.790000 |
| 3 | Yadel | 705.90 | 141.180000 |
| 5 | Topiclounge | 980.02 | 163.336666 |