Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more


SQL Subquery

Posted on 2011-09-13
Medium Priority
Last Modified: 2012-05-12
I need a query that will return the sum of the largest unpaid invoices for each vendor. The following query returns 22325.39:

SELECT SUM(MaxInvoice)
FROM Invoices JOIN
      (SELECT VendorID, MAX(InvoiceTotal) AS MaxInvoice
      FROM Invoices
      WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
      GROUP BY VendorID) AS InvoiceSub
      ON Invoices.InvoiceTotal = InvoiceSub.MaxInvoice

However, when I just run the subquery and add a ROLLUP, I get this:

SELECT VendorID, MAX(InvoiceTotal) AS MaxInvoice
      FROM Invoices
      WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0

37      224.00
72      85.31
80      90.36
83      579.42
106      503.20
110      20551.18
123      67.92
NULL      20551.18

This leads me to believe that my complete query isn't correct. Can anyone show me what I did wrong?
Question by:fieldb1
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 32

Accepted Solution

Ephraim Wangoya earned 2000 total points
ID: 36533277
SELECT SUM(MaxInvoice)
	SELECT MAX(InvoiceTotal) AS MaxInvoice
	FROM Invoices 
	WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
) A

Open in new window


Author Closing Comment

ID: 36533294
Thank you.

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

647 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question