Hey guys,
I just got in a SQL class and its for ultranoobs like me =)

Anyhow, I was struggling with a concept. We are just doing basic queries and using MS Access.
I have a database that includes 5 attributes:

1) AccidentDate (Year, Number)
2) AccidentLocation (Street, Text String)
3) Damages (Currency, Number)
4) ReportNumber (Number)
5) CarLicense (Number)

It is a relation that describes an accident ticket for an insurance company.

My goal is to return the LOCATION of the accident with the highest damages in 2006.

So far I can get the highest damages easily with the following code:




But if I put anything in like the following, it tells me that AccidentLocation is not in the aggregate function...whatever that means....




I just don't know how to get it to show the location when its not part of the logic at all....

Any help would be greatly appreciated!

Functions like MAX, SUM, COUNT etc are known as "aggregate" functions. Meaning they somehow group records together (aggregated results). In the case of your max function in the first query you are grouping your records together and finding the max value based on a year of 2006. When you add the second field, it tries to group on the damages, but can't because your location field is being included and they are different.

So what you need to do is include the field name not only in the select list, but also need a group by clause for any fields which are not in that aggregate function (the max field). This tells the query that you want to include the other field and what it will do is create a max for damages for each unique type of location. Lets say you had three accidents at the corner of 4th and pine and three accidents at broadway and wilsher... it will find the max damages for the first accident location and then find the max for the second location. The result is that you have to maximums, one for each accident location.

I hope that makes sense to you. Just know that if you have an aggregate function in your select field list, along with other fields not in the aggregate, you are going to need a group by clause for those fields to group on.

Example below... this would group them as mentioned above, max damage for each location...



Notice how I group on the field that is NOT in the aggregate function MAX.

This post has been edited by Martyr2: 12 Oct, 2008 - 03:21 PM

Yeah. This is super frustrating. If I group by AccidentLocation, it works and shows both a Location and the Max damage for that location. However, it shows EACH max for a given collection of locations.

I believe the assignment is fairly clear on it only returning one location. The assignment makes us create our own database, so a bit of interpretation of the questions is necessary, but I am pretty certain on this one.

So I am wondering now if I am going about this the wrong way entirely with MAX().

Thanks for the good start, though, Martyr



This post has been edited by killnine: 12 Oct, 2008 - 03:20 PM

In case you missed it, I added a little more onto my previous remark with an example.

Is this simply a problem that cannot be done without embedding a query?

I am thinking the only way to return a single Location is to create another query containing the query Martyr explained and use it to remove all but the highest Damages value.

Thoughts?

I have found that virtually all of my questions require me to return an attribute entirely unrelated to the attribute being compared: "Name of owner who owns most cars", "Location with greatest dmg", etc. This is just beyond me.

This post has been edited by killnine: 12 Oct, 2008 - 03:42 PM

Well if you think about it you are essentially asking two questions at once in a compound question... which record has the greatest damage and what what location did that accident happen at. So it makes very good sense that you would need a nested query in this. Don't you think?