CODEWITHSUNDEEP
sql-servervb6
greenback
greenback

Reputation: 1615

SQL query to return maximum values from multiple columns

I have the following tables in a SQL Server 2008 R2 database:

Customers
=========
CustID  CustName
======  ========
0       NULL
1       A
2       B
3       C

InterestingCustomers
====================
CustID
======
0
1
3

Orders
======
CustID  OrderID  InvoiceTotal  Discount
======  =======  ============  ========
0       NULL     2000          NULL
0       100      NULL          500      
1       1        100           NULL
1       2        90            15
2       3        300           25
2       4        50            0
3       5        100           10
3       6        200           25
3       7        150           NULL
3       8        120           20

I want a query that shows the maximum value for both the InvoiceTotal and Discount columns for each CustID and CustName across all orders, but only if the customer appears in InterestingCutomers and has ID > 0:

CustID  CustName  MaxInvoiceTotal  MaxDiscount
1       A         100              15
3       C         200              25

One restrition I have is that my application is legacy VB6 using Jet 4.0 and ADO. This lacks support for many of the advanced features of SQL Server 2008 where my database is hosted. The SQL functions I have available are listed here: https://support.microsoft.com/en-us/kb/294698.

So far I have been able to get the max InvoiceTotal but I cannot extend the same query to work to return the maximum of any other column.

The following correctly returns the maximum InvoiceTotal for each valid CustID:

SELECT Customers.CustID, CustName, InvoiceTotal, Discount 
FROM ((Customers INNER JOIN Interesting ON Customers.CustID = Interesting.CustID) INNER JOIN Orders ON Customers.CustID = Orders.CustID) 
WHERE ((CustID > 0) 
AND (InvoiceTotal IN 
(SELECT MAX(InvoiceTotal) AS MaxInvoiceTotal 
FROM Orders 
WHERE Orders.CustID = Customers.CustID)))

Returns:

CustID  CustName  MaxInvoiceTotal
1       A         100            
3       C         200

However, extending the WHERE clause to restrict Discount to the maximum value for each customer only returns records where both the InvoiceTotal and Discount happen to be the maximum and belong to the same order:

WHERE ((CustID > 0) AND (InvoiceTotal IN 
(SELECT MAX(InvoiceTotal) AS MaxInvoiceTotal 
FROM Orders 
WHERE Orders.CustID = Customers.CustID)) AND 
Discount IN 
(SELECT MAX(Discount) AS MaxDiscount 
FROM Orders 
WHERE Orders.CustID = Customers.CustID))

Returns:

CustID  CustName  MaxInvoiceTotal  MaxDiscount
3       C         200              25

CustID 1 is not selected because its maximum InvoiceTotal value belongs to a different order from its maximum Discount value. I assume both conditions are tested against each order that qualified to get this far, but I would like some way of removing the association between the OrderID and the InvoiceTotal and Discount. OR-ing the additional clause doesn't work.

I suspect I may be in UNION territory here but wanted to check if there is a cleaner or more efficient way of doing this, given the restrictions I have outlined.

Upvotes: 1

Views: 409

Answers (2)

Earl
Earl

Reputation: 480

SELECT CustID,
       CustName = MAX(CustName),
       InvoiceTotal = SUM(InvoiceTotal),
       Discount = SUM(Discount)
FROM Orders O
INNER JOIN Customers C ON O.CustID = C.CustID
WHERE O.CustID IN (SELECT CustID 
                   FROM InterestingCustomers)
AND O.CustID>0
GROUP BY CustID

Upvotes: 0

gunvant.k
gunvant.k

Reputation: 1086

SELECT Customers.CustID, CustName, MaxInvoiceTotal, MaxDiscount 
FROM Customers 
INNER JOIN Interesting ON Customers.CustID = Interesting.CustID
INNER JOIN (SELECT CustID, MAX(Discount) AS MaxDiscount 
            FROM Orders
            Group by CustID) MaxDiscountOrders ON Customers.CustID =     
            MaxDiscountOrders.CustID
INNER JOIN (SELECT CustID, MAX(InvoiceTotal) AS MaxInvoiceTotal 
            FROM Orders
            Group by CustID) MaxInvoiceTotalOrders ON Customers.CustID     
            = MaxInvoiceTotalOrders.CustID
WHERE CustID > 0

Try this.. I haven't checked syntax error though.

Upvotes: 1

Related Questions