Solved

SQL Subquery

Posted on 2011-09-13
2
372 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
2 Comments
 
LVL 32

Accepted Solution

by:
ewangoya 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now