CODEWITHSUNDEEP
sqlsql-serverssms
nicomp
nicomp

Reputation: 4647

What is wrong with this nested SELECT statement compared to the one that works?

I am using the AP database distributed by Microsoft. This works:

USE AP
SELECT V.VendorName, InvoiceDate, InvoiceTotal
FROM Invoices I 
INNER JOIN Vendors V ON I.VendorID = V.VendorID
WHERE InvoiceTotal > 
    (SELECT AVG(InvoiceTotal)
     FROM Invoices)
ORDER BY InvoiceTotal

This doesn't work:

SELECT AP.dbo.Invoices.InvoiceTotal, AP.dbo.Vendors.VendorName, AP.dbo.Invoices.InvoiceDate
FROM     AP.dbo.Invoices INNER JOIN
                  AP.dbo.Vendors ON AP.dbo.Invoices.VendorID = AP.dbo.Vendors.VendorID
WHERE  AP.dbo.Invoices.InvoiceTotal >
                      (SELECT AVG(AP.dbo.Invoices.InvoiceTotal) AS Expr1
                       FROM      AP.dbo.Invoices AS Invoices_1)

The error I get on the above query is this:

Msg 147, Level 15, State 1, Line 5 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270693

Use table aliases!

SELECT i.InvoiceTotal, v.VendorName, i.InvoiceDate
FROM AP.dbo.Invoices i INNER JOIN
     AP.dbo.Vendors v
     ON i.VendorID = v.VendorID
WHERE i.InvoiceTotal > (SELECT AVG(i2.InvoiceTotal)
                        FROM AP.dbo.Invoices i2
                       );

Your code doesn't work because the subquery is not referring to the table in the subquery. That would be invoice_1.InvoiceTotal rather than what you have. The reference to the outer query is not allowed with an aggregation function.

Upvotes: 3

Related Questions