sql question

Second highest invoice for each customer Total invoices for each customer per month SELECT DISTINCT Customers.CustomerId, Customers.CustomerName, Invoice1.InvoiceId, Invoice1.InvoiceAmount FROM Customers JOIN Invoices AS Invoice1 ON Customers.CustomerId = Invoice1.CustomerId JOIN Invoices AS Invoice2 ON Customers.CustomerId = Invoice2.CustomerId AND Invoice1.InvoiceAmount < Invoice2.InvoiceAmount GROUP BY Customers.CustomerId, Customers.CustomerName, Invoice1.InvoiceId, Invoice1.InvoiceAmount HAVING COUNT(DISTINCT Invoice2.InvoiceAmount) = 1 SELECT CustomerID, FORMAT(InvoiceDate, 'yyyy-MM') AS InvoiceMonth, SUM(InvoiceValue) AS TotalInvoiceAmount FROM Exam.Invoice GROUP BY CustomerID, FORMAT(InvoiceDate, 'yyyy-MM');

May 5, 2025 - 22:48

Second highest invoice for each customer
Total invoices for each customer per month

SELECT DISTINCT
Customers.CustomerId,

Customers.CustomerName,

Invoice1.InvoiceId,

Invoice1.InvoiceAmount

FROM
Customers

JOIN
Invoices AS Invoice1

ON Customers.CustomerId = Invoice1.CustomerId
JOIN
Invoices AS Invoice2

ON Customers.CustomerId = Invoice2.CustomerId
AND Invoice1.InvoiceAmount < Invoice2.InvoiceAmount
GROUP BY
Customers.CustomerId,

Customers.CustomerName,

Invoice1.InvoiceId,

Invoice1.InvoiceAmount

HAVING
COUNT(DISTINCT Invoice2.InvoiceAmount) = 1

SELECT
CustomerID,
FORMAT(InvoiceDate, 'yyyy-MM') AS InvoiceMonth,
SUM(InvoiceValue) AS TotalInvoiceAmount
FROM Exam.Invoice
GROUP BY CustomerID, FORMAT(InvoiceDate, 'yyyy-MM');