Correlated Subquery SQL Server 2014

Question

I am (unsuccessfully) using a correlated subquery on an Invoices table:

Invoices(InvoiceID,  VendorID, InvoiceTotal, PaymentTotal, CreditTotal,.... ),

to find the sum of largest unpaid invoices by all vendors, where the unpaid condition is given by InvoiceTotal-PaymentTotal-CreditTotal <0:

 Select Sum(LargestUnpaid) from 
 (Select   Max(InvoiceTotal) AS LargestUnpaid from Invoices 
  where InvoiceTotal-(PaymentTotal+CreditTotal)<0 group by vendorID ) ;

The inner query runs , which is good and bad, since the query is supposed to be correlated , but the query as a whole does not run, and I get the error message:

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ')'.

What am I doing wrong?

Answer 1

Add an alias for the derived table before the ; for example VendLargestUnpaidInv:

Select Sum(LargestUnpaid) from
 (Select   Max(InvoiceTotal) AS LargestUnpaid from Invoices 
  where InvoiceTotal-(PaymentTotal+CreditTotal)<0 group by vendorID ) VendLargestUnpaidInv;

Answer 2

Or to make it more readable, using CTE (Common Table Expression)

With VendLargestUnpaidInv as 
( 
    Select   Max(InvoiceTotal) AS LargestUnpaid from Invoices 
    where InvoiceTotal - (PaymentTotal+CreditTotal)<0 
    group by vendorID
)
select sum(LargestUnpaid) 
from VendLargestUnpaidInv;