• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 724
  • Last Modified:

Access "Aggregate Function Error"

when I try to run the query below, it generates an error saying that I tried to execute query that does not include the specified expression SumOfInspected as part of an aggregate function.

Could someone please explain what I am doing wrong here? I seem to chase my tail with this error.
SELECT GetWeekEndingDate([ProductionDate]) AS WeekEndingDate, SumOfInspected, SumOfGood, (SumOfGood/SumOfInspected)*100 AS CalculatedFPY
FROM EG_Wood_FPY_Trend_uqry
GROUP BY GetWeekEndingDate([ProductionDate])
ORDER BY GetWeekEndingDate([ProductionDate]);

Open in new window

0
Rex85
Asked:
Rex85
  • 3
  • 2
1 Solution
 
harfangCommented:
You are writing a total query. In such a query, each field used in the SELECT clause must either:
* be enclosed in a domain aggregate function: sum(), min(), max(), avg(), etc.
* be repeated in the GROUP BY section

In other words, you could write:

SELECT
  GetWeekEndingDate([ProductionDate]) AS WeekEndingDate,
  SumOfInspected,
  SumOfGood,
  (SumOfGood/SumOfInspected)*100 AS CalculatedFPY
FROM EG_Wood_FPY_Trend_uqry
GROUP BY
  GetWeekEndingDate([ProductionDate]),
  SumOfInspected,
  SumOfGood
ORDER BY GetWeekEndingDate([ProductionDate]);

OR

SELECT
  GetWeekEndingDate([ProductionDate]) AS WeekEndingDate,
  Sum(SumOfInspected) As SumOfSumOfInspected,
  Sum(SumOfGood) As SumOfSumOfGood,
  Avb((SumOfGood/SumOfInspected)*100) AS AvgOfCalculatedFPY
FROM EG_Wood_FPY_Trend_uqry
GROUP BY
  GetWeekEndingDate([ProductionDate])
ORDER BY GetWeekEndingDate([ProductionDate]);

OR, better

Reopen your original total query, EG_Wood_FPY_Trend_uqry, which already performs the two sums, and adjust that one instead to group by "week ending date".

(°v°)
0
 
Rex85Author Commented:
Thank you for stating the requirements so clearly. However, I'm afraid I don't undertand how to aplly them.

EG_Wood_FPY_Trend_uqry is a UNION Query that pulls together several sources. (See below) I am already Grouping by the week ending date, but do not know how to handle the calculated field. that's why I was trying the two step query approach.

SELECT GetWeekEndingDate([ProductionDate]) AS WeekEndingDate,Sum(Inspected) AS SumofInspected, Sum(Good) AS SumofGood
FROM [Archive 15th St L1 FPY Weekly Trend]
GROUP BY GetWeekEndingDate([ProductionDate])
UNION ALL SELECT GetWeekEndingDate([ProductionDate]) AS WeekEndingDate,Sum(Inspected) AS SumofInspected, Sum(Good) AS SumofGood
FROM  [Archive 15th St L2 FPY Weekly Trend]
GROUP BY GetWeekEndingDate([ProductionDate])
UNION ALL SELECT GetWeekEndingDate([ProductionDate]) AS WeekEndingDate, Sum(FifteenthStqryWoodFPY.Inspected) AS SumOfInspected, Sum(FifteenthStqryWoodFPY.Good) AS SumOfGood
FROM FifteenthStqryWoodFPY
GROUP BY GetWeekEndingDate([ProductionDate]);
0
 
harfangCommented:
This is strange.

The UNION query should return these fields:

    WeekEndingDate, SumofInspected, and SumofGood

So that the first query you showed should not work at all: there is no field called ProductionDate in the source.

I don't know where the problem is, but, if your dates from the three queries in the UNION cannot overlap, you don't need an enclosing total query. Use:

SELECT WeekEndingDate, SumOfInspected, SumOfGood, (SumOfGood/SumOfInspected)*100 AS CalculatedFPY
FROM EG_Wood_FPY_Trend_uqry;

If the dates can overlap, you could perform the grouping in the main query, and use a simple UNION as subquery:

SELECT
  GetWeekEndingDate(ProductionDate) As WeekEndingDate,
  Sum(Inspected) As SumofInspected,
  Sum(Good) As SumofGood,
  Sum(Good)/Sum(Inspected)*100 As CalculatedFPY
FROM (
    SELECT ProductionDate, Inspected, Good
    FROM [Archive 15th St L1 FPY Weekly Trend]
    UNION ALL SELECT ProductionDate, Inspected, Good
    FROM  [Archive 15th St L2 FPY Weekly Trend]
    UNION ALL SELECT ProductionDate, Inspected, Good
    FROM FifteenthStqryWoodFPY
    )
GROUP BY GetWeekEndingDate(ProductionDate)

Note: the subquery (everything between the main brackets of the FROM clause) can be a stored query as well, for example MyUnionQuery, so that you would then simply use

    FROM MyUnionQuery

Finally, the second example in my first comment, as goofy as it looks, could work as well. You perform subtotals in each query of the UNION query, and a grand total in the main enclosing query. In effect "sum of sum of...", although you can find better names. The average would not be correct, but you could again use a quotient of sums to obtain the percentage.

Does that help any?

(°v°)
0
 
Rex85Author Commented:
I used the second example with the subquery. You are also correct in my not having the ProductionDate field. I found that after I posted my question.

Thank you very much. You have been very helpful.
0
 
harfangCommented:
You are welcome. Thank you for the feedback, and success with your project!
(°v°)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now