Odd Grouping/Summation Situation - Subquery

[rubbernilly]

I have an odd situation. I have three tables that matter

WarrantyInformation
Vehicles
VehicleVolumes

A Vehicle entry can be used multiple times in the Warranty table, and multiple times in the Volumes table. The VehicleVolumes table has a primary key of Plant, ModelCode, and VolumeMonth, so that each entry is the Volume for that Plant, making that vehicle, this month.

What I need to be able to do is get a Total Volume from the VehicleVolumes table for any particular aggregation from the Warranty Table.

For instance, if I group on Vehicle, PartGroup, and TIS (time in service), I need the Vehicle Volume for the claims that match that. I cannot do a Sum(Volume), since that gives me multiples of the volume I should have. For example, I could have two records that match the grouping (Vehicle=1, PartGroup=87300, and TIS=6). If those two records are from the same Plant and ProdMonth (the other Primary Keys of the Volume table), then I only want the Volume once - they are both a part of the same production volume for the same plant for the same month for the same vehicle. A Sum(Volume) would give me twice the Volume I am actually looking for. Only if one of the records is from a different plant or different month of production would I then want a second month of Volume numbers, and then I would only want it once no matter how many records match that grouping.

I have tried solving this with a subquery to get the Sum(Volume) as an expression. I have a hundred queries, so I would prefer to solve this with a subquery rather than a pre-query. Here is my field declaration from the query I have been working with first:

Total Volume: (SELECT Sum(V.Volume) AS SumOfVolume FROM VehicleVolumes AS V WHERE (((V.VehicleCode)=[W].[ModelCode]) AND ((V.VolumeMonth)=[W].[ProdMonth]) AND ((V.Plant)=[W].[Plant]));)

And here is the SQL from the Query:

SELECT W.VehicleName, W.ModelCode, W.PlantName, W.ModelYear, W.TIS, (SELECT Sum(V.Volume) AS SumOfVolume FROM VehicleVolumes AS V WHERE (((V.VehicleCode)=[W].[ModelCode]) AND ((V.VolumeMonth)=[W].[ProdMonth]) AND ((V.Plant)=[W].[Plant]));) AS [Total Volume], Sum(W.TotalWarrantyCost) AS [Total Cost], [Total Cost]/[Total Volume] AS CPU
FROM adm_WarrantyQuery_ProdMonth AS W
GROUP BY W.VehicleName, W.ModelCode, W.PlantName, W.ModelYear, W.TIS, W.Plant
HAVING (((W.TIS) Between [TIS Lower Limit] And [TIS Upper Limit]))
ORDER BY W.VehicleName, W.ModelCode, W.PlantName, W.ModelYear, W.TIS;

The problem is that this field declaration generates the following error:

[maroon]"You tried to execute a query that does not include the specified expression '' as part of an aggregate function."[/maroon]

If I change the field declaration of Total Volume to be this:

Total Volume: (SELECT Sum(V.Volume) AS SumOfVolume FROM VehicleVolumes AS V WHERE (((V.VehicleCode)=[W].[ModelCode]) AND ((V.VolumeMonth) [blue][b]Between [Start Date] AND [END DATE][/b][/blue]) AND ((V.Plant)=[W].[Plant]));)

...then the query runs fine, except that this gets me the wrong Volume total.

So it seems that the problem is with the [W].[ProdMonth] field. The thing is, even if I add the [W].[ProdMonth] to the grouping of the main query, I get the same error.

What am I missing?

 

[lespaul]

For every field in your SELECT clause that is not an aggregate (COUNT, SUM, AVG, etc.) must be listed in the GROUP BY clause. Additionally, the HAVING clause is used elimate records based on some kind of aggregate.

SELECT Field1, Count(Field2) FROM TableName WHERE SomeCondition Between Something AND SomethingElse GROUP BY Field1 HAVING Count(Field2) > 10

Another thing, in your subquery you still have the ending ';' - that needs to be removed.

Why don't you post some sample data from your three tables and provide the expected results from that table. That really makes it easier to help (unless PHV answers, he doesn't need additional information, but it helps the rest of us!)






Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[rubbernilly]

Thanks for the response, Leslie. Let me give you some more information...

Let's say the Altima Madison 2006 vehicle has one and only one entry in the VehicleVolume table for the month of 2/1/2006:

|-------------------------+----------+--------|--------|
|         Vehicle         |  PLANT   |  MOP   | VOLUME |
|-------------------------+----------+--------+--------|
     Altima Madison 2006  |   123    |2/1/2006|  10,000

Let's also say that there are four records in the Warranty table that reference this vehicle and this Production Month (there are other fields in the Warranty table, but these are the ones that matter for now):

|-------------------------+----------+--------+------------|
|         Vehicle         |  5DigPN  |  TIS   |    MOP     |
|-------------------------+----------+--------+------------|
 Altima Madison 2006      |  87300   |   0    |    2/1/2006
 -------------------------+----------+--------+------------
 Altima Madison 2006      |  87300   |   1    |    2/1/2006
 -------------------------+----------+--------+------------
 Altima Madison 2006      |  87300   |   1    |    2/1/2006
 -------------------------+----------+--------+------------
 Altima Madison 2006      |  87300   |   1    |    2/1/2006

(5DigPN = PartGroup)

All of the vehicles reported in these records were part of that one Volume output from the one plant in Feb '06. But if I construct a query and group on the first three fields above, then sum the volume for that month and vehicle, I would get an output like this:

|-------------------------+----------+--------+------------|
|         Vehicle         |  5DigPN  |  TIS   |   Volume   |
|-------------------------+----------+--------+------------|
 Altima Madison 2006      |  87300   |   0    |      10,000
 -------------------------+----------+--------+------------
 Altima Madison 2006      |  87300   |   1    |      30,000

Obviously that is not right. Both entries should be 10,000 for the Volume.

The solution would seem to be to Group on the Volume, except that when the MOP is not included in the query and the TIS is included, then there are multiple Volume numbers that need added. For instance, the Altima Madison 2006 vehicle could have a TIS of 0 in Feb for ten records, and a TIS of 0 in March for another 4, and a TIS of 0 for April in another 6. Now when I want the volume for TIS of 0, I want the Sum of Feb, March, and April, but only one instance of each (rather than 10xFeb, 4xFeb, and 6xApril).

So, even though I am only grouping on Vehicle, 5DigPN, and TIS in my main query, I'll add a grouping level below for MOP so that we can see the various months that comprise the total Volume. (Note that because there is only one plant entry for this vehicle, I do not have to include it at this point... Vehicle and MOP are unique by virtue of grouping, and the Plant will be unique because it is the only plant for this vehicle.)

Here is the rest of the VehicleVolume data for the Altima Madison 2006:

|-------------------------+----------+--------|--------|
|         Vehicle         |  PLANT   |  MOP   | VOLUME |
|-------------------------+----------+--------+--------|
     Altima Madison 2006  |   123    |2/1/2006|  10,000
|-------------------------+----------+--------+--------|
     Altima Madison 2006  |   123    |3/1/2006|  10,200
|-------------------------+----------+--------+--------|
     Altima Madison 2006  |   123    |4/1/2006|   9,600

Given that (and similar data for the Frontier 2006), the following query includes the MOP, as I said above:

|-------------------------+----------+--------+------------+--------------|
|         Vehicle         |  5DigPN  |  TIS   |    MOP     |    Volume    |
|-------------------------+----------+--------+------------+--------------|
 Altima Madison 2006      |  87300   |   0    |    2/1/2006|        10,000
 -------------------------+----------+--------+------------+--------------
 Altima Madison 2006      |  87300   |   0    |    3/1/2006|        10,200
 -------------------------+----------+--------+------------+--------------
 Altima Madison 2006      |  87300   |   1    |    3/1/2006|        10,200
 -------------------------+----------+--------+------------+--------------
 Altima Madison 2006      |  87300   |   1    |    4/1/2006|         9,600
 -------------------------+----------+--------+------------+--------------
 Altima Madison 2006      |  87300   |   2    |    4/1/2006|         9,600
 -------------------------+----------+--------+------------+--------------
 Frontier 2006            |  92210   |   0    |    1/1/2006|        11,000
 -------------------------+----------+--------+------------+--------------
 Frontier 2006            |  92210   |   1    |    1/1/2006|        11,000
 -------------------------+----------+--------+------------+--------------
 Frontier 2006            |  90100   |   0    |    1/1/2006|        11,000
 -------------------------+----------+--------+------------+--------------
 Frontier 2006            |  90100   |   1    |    1/1/2006|        11,000
 -------------------------+----------+--------+------------+--------------
 Frontier 2006            |  90100   |   1    |    2/1/2006|        10,500

Since the MOP is there only so that I can determine the proper volume, this data would compress into the following output:

|-----------------------+---------+-------+-------------+----------------|
|        Vehicle        | 5DigPN  |  TIS  |   Volume    |  Explanation   |
|-----------------------+---------+-------+-------------+----------------|
 Altima Madison 2006    |  87300  |   0   |       20,200|   10,000+10,200
 -----------------------+---------+-------+-------------+----------------
 Altima Madison 2006    |  87300  |   1   |       19,800|    10,200+9,600
 -----------------------+---------+-------+-------------+----------------
 Altima Madison 2006    |  87300  |   2   |        9,600|  Only one entry
 -----------------------+---------+-------+-------------+----------------
 Frontier 2006          |  92210  |   0   |       11,000|  Only one entry
 -----------------------+---------+-------+-------------+----------------
 Frontier 2006          |  92210  |   1   |       11,000|  Only one entry
 -----------------------+---------+-------+-------------+----------------
 Frontier 2006          |  90100  |   0   |       11,000|  Only one entry
 -----------------------+---------+-------+-------------+----------------
 Frontier 2006          |  90100  |   1   |       21,500|   11,000+10,500

This is the final look of what I want. I need to be able to do this no matter if the MOP is included in the grouping or not, and/or no matter if the Plant is included in the grouping or not.

 

[lespaul]

try this query:

SELECT W.VEHICLE, 5DIGPN, TIS, SUM(VOLUME) FROM WARRANTY W INNER JOIN VEHICLEVOLUME V ON W.VEHICLE = V.VEHICLE AND W.MOP = V.MOP WHERE W.TIS Between [TIS Lower Limit] And [TIS Upper Limit] GROUP BY W.VEHICLE, 5DIGPN, TIS

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[rubbernilly]

That query would return the sum of all the corresponding Volumes for each individual record in the Warranty table, and is in fact one of my first queries that I build from. My question is the next step - once grouping and aggregation begins.

Once I begin to group on 5DigPN and/or TIS, for example, I am going to have multiple records being grouped together.

What do I do with the Volume then?
--can't Sum(Volume), as that will be too high (a multiple or the sum of multiples)
--can't Group By Volume, since there may be multiple months and Volume numbers involved in the group
--can't do Min(Volume), Max(Volume), First(Volume), Last(Volume) or Avg(Volume), for the same reason as Group By, above


What I need is the Sum of the Volume after I have grouped on the Volume... or more specifically on the Vehicle, Plant, and Month of Production (since I suppose that the same plant could produce the same number of the same vehicle type in two different months).

 

[lespaul]

What I need is the Sum of the Volume after I have grouped on the Volume

then you need to create a query that has all the information you need without the SUM and then create a second query the sums the information from the first query.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[rubbernilly]

Which is exactly what I was trying to avoid. There are 100+queries that this will be needed for.

 

[PHV]

What about adapting Leslie's suggestion like this ?
SELECT V.VEHICLE, W.5DIGPN, W.TIS, Sum(V.VOLUME)
FROM VEHICLEVOLUME V INNER JOIN (
SELECT DISTINCT VEHICLE, 5DIGPN, TIS, MOP FROM WARRANTY
) W ON V.VEHICLE = W.VEHICLE AND V.MOP = W.MOP
WHERE W.TIS Between [TIS Lower Limit] And [TIS Upper Limit]
GROUP BY V.VEHICLE, W.5DIGPN, W.TIS

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