...

View Full Version : Aggregate Function problem...



parallon
03-20-2007, 08:27 PM
Hello all. I didn't find a forum for regular SQL, so I figured I would try here. I have the following statement which I am having problems with:



SELECT Max(odThis) as ThisOD, Max(odLast) as LastOD, id FROM tblEqOdometer WHERE EqNum = '" & EqNum & "' group by Id


The thing is that I need the ID for another process, but using the Group By Id it is not giving me the Max(odThis) that I need, it seems like it is giving the first record that it comes across. Is there a way to either get rid of the Group By or do an Order By the Id? I really don't need them grouped, but it is forcing me to since I used the Sum() function.

Thanks,

Parallon

Roelf
03-20-2007, 09:07 PM
the way the query works now, is select all records where the WHERE part of the query applies. Then group them by Id and for each group, pick the maximum value for odThis and pick the maximum value for odLast in that particular group.

ANd indeed, if you use aggregate functions on certain requested columns, you need to group on the columns where no aggregate function is performed.

If you can define functionally which result you expect, i can try to put up the query you need. If you can post some sample data and the desired result, that would give me a good start.

THis could have been posted in the General Databases (http://www.codingforums.com/forumdisplay.php?f=38)forum

parallon
03-20-2007, 10:04 PM
Thank you for your reply. Here is some sample data. What I need the system to do is to find the Highest odometer reading in the 'OdThis' column and use that line item. The reason I need the ID is so when the user Submits the report, I can change the value of the 'Submitted' column for the appropriate ID.

So, with this query, I am getting a result of 1000 instead of 2500.


ID EqNum WeekStart WeekEnd OdLast OdThis Submitted
178 TRK-0001 3/7/2007 3/13/2007 0 1000 No
180 TRK-0001 3/14/2007 3/20/2007 1000 1500 No
182 TRK-0001 3/21/2007 3/27/2007 1500 2100 No
183 TRK-0001 3/28/2007 4/3/2007 2100 2500 No

Thanks again,

Parallon

Roelf
03-21-2007, 07:41 AM
SELECT Id
FROM tblEqOdometer
WHERE (
OdThis = (SELECT MAX(odthis) FROM tblEqOdometer)
)
This returns the Id (or more than one Id) for the record with the highest value for odthis

SSJ
03-21-2007, 10:13 AM
Try this...


SELECT Id
FROM tblEqOdometer
WHERE (
OdThis In (SELECT MAX(odthis) FROM tblEqOdometer)
)

parallon
03-21-2007, 04:05 PM
Thank you both so much for leading me in the right direction. I did have to modify it slightly since I was using other fields elsewhere within my page. Here is what I came up with:


SELECT ID, OdThis AS ThisOD, OdLast AS LastOD
FROM tblEqOdometer
WHERE (
OdThis=(SELECT MAX(odthis) FROM tblEqOdometer)
)

Works like a charm!

Thanks again,

Parallon



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum