Sum Numbers Then Return Highest

[Aidy680]

I have the following query:

SELECT tblDifferentDates.FixDate, tblDifferentDates.BookCode, tblDifferentDates.HAH, Sum(tblDifferentDates.Quantity) AS SumOfQuantity
FROM tblDifferentDates
GROUP BY tblDifferentDates.FixDate, tblDifferentDates.BookCode, tblDifferentDates.HAH
HAVING (((tblDifferentDates.FixDate) Is Not Null))
ORDER BY tblDifferentDates.HAH, Sum(tblDifferentDates.Quantity) DESC;

which returns a set of records of the following nature:

FixDate BookCode HAH SumOfQuantity
27-Feb-07 1111 Test1 14370
27-Feb-07 2222 Test1 3816
27-Feb-07 3333 Test1 0
27-Feb-07 Ex1 Test1 0
27-Feb-07 Ex2 Test1 -27
27-Feb-07 4444 Test1 -2036

My question is how would I mod the SQL to only return the record that has the highest SumOfQuantity?

Bear in mind that the query also returns a heap of records where the HAH field is Test2 and I would like to return the record with the highest SumOfQuantity amount for these set of records also.

My point is, I think that this rules out the "TOP" option doesn't it?

Pls help!

 

[Aidy680]

OK I can run another query which will get the MAX of the SumOfQuantity field, and will display all the other fields, apart from when I include "Bookcode".

I can see why it does this (as all the bookcodes are different and it's grouping) but when I try and set the field to one of the other options (such as "Expression") it just wont have it and falls over.

My SQL (which works) is as such:

SELECT zzz1.FixDate, zzz1.HAH, Max(zzz1.SumOfQuantity) AS MaxOfSumOfQuantity FROM zzz1
GROUP BY zzz1.FixDate, zzz1.HAH
ORDER BY zzz1.FixDate, zzz1.HAH;

However how do I include the Bookcode that is associated with the highest SumOfQuantity value, without displaying all of the different Bookcodes in the results?

Hope this makes sense.

 

[PHV]

Create a query named, say, qrySumQty:
SELECT FixDate, BookCode, HAH, Sum(Quantity) AS SumOfQuantity
FROM tblDifferentDates
WHERE FixDate Is Not Null
GROUP BY FixDate, BookCode, HAH

Create a query named, say, qryMaxQty:
SELECT HAH, Max(SumOfQuantity) AS MaxOfQuantity
FROM qrySumQty
GROUP BY HAH

And now your query:
SELECT S.FixDate, S.BookCode, S.HAH, S.SumOfQuantity
FROM qrySumQty AS S INNER JOIN qryMaxQty AS M ON S.HAH = M.HAH AND S.SumOfQuantity = M.MaxOfQuantity
ORDER BY S.HAH, S.SumOfQuantity DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Aidy680]

Thanks PHV.

I think I got there the same time as you.

The main problem I think, is that I'm always looking to create the perfect query rather than having to create multiple instances.

Sometimes though, needs must eh....