[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

SQL Subquery

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
fieldb1
Asked:
fieldb1
1 Solution
 
Ephraim WangoyaCommented:
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
 
fieldb1Author Commented:
Thank you.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now