Solved

SQL Subquery

Posted on 2011-09-13
2
391 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 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