Solved

SQL Subquery

Posted on 2011-09-13
2
408 Views
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
      GROUP BY VendorID WITH ROLLUP

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?
0
Comment
Question by:fieldb1
[X]
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
2 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 36533277
try
SELECT SUM(MaxInvoice)
FROM
(
	SELECT MAX(InvoiceTotal) AS MaxInvoice
	FROM Invoices 
	WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
	GROUP BY VendorID
) A

Open in new window

0
 

Author Closing Comment

by:fieldb1
ID: 36533294
Thank you.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

740 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